How to highlight data in Excel using worksheet change event

A user wants to highlight data automatically in specific cells in an Excel worksheet based on a date that he enters in a specific cell. We can achieve this goal by using the worksheet change event.

Watch the training video:

Watch this video on YouTube.

Here’s the complete VBA code for the worksheet change event to highlight specific data automatically:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
Dim lastrow As Long
lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
If Target.Column = 3 Then
For i = 2 To lastrow
If IsDate(Cells(i, “C”)) = False Then
Range(“A” & i & “:” & “C” & i).Interior.ColorIndex = xlNone
Range(“A” & i & “:” & “C” & i).Font.ColorIndex = 1
Else
‘red 3, blue 37
Range(“A” & i & “:” & “C” & i).Interior.ColorIndex = 3
Range(“A” & i & “:” & “C” & i).Font.ColorIndex = 2
End If
Next
End If
‘http://dmcritchie.mvps.org/excel/colors.htm – for all color indices
End Sub

How to highlight data in Excel using worksheet change event.

How to highlight data in Excel using worksheet change event.

One thought on “How to highlight data in Excel using worksheet change event

  1. Baljit

    Hi Sir

    Im am trying to write VBA code. I have a excel with 2 sheets named Current trades and opening position and third sheet i have added just for example of output. I want to create code based on following codition. Can anyone help me…It would be really kind. I generally can read, understand and manupulated the VBA code but lacks in logical coding.
    1. if there is new trade “buy” and open position is “Long” then action1 =”Buy” and action2= “Open. For example reliance (2210) -should check in open position and there we have 1 long posiition , so action1 =”Buy” and action2= “Open.
    2. if there is new trade “sell” trade and open Position is also “Short”, then action1 =”sell” and action2= “Open , Example Wipro (2300”) is having 1 “sell” trade and Open postiong is also short, so should be action1 =”sell” and action2= “Open ,
    3. if there is new security trade like Tesco 2410 (Sell), – and does not have any open positon it must be action1 =”sell” and action2= “Open and vice versa for APPLE 1564 – for buy security without open postion – action1 =”buy” and action2= “Open ,
    4. if there are several new sell trades, and opening position is long and each line of new trade must be subtracted from long position untill get ZERO – for example TCS (2413) is ‘having differenct trades let’s see first trade row is having 4 units the LONG opening positions are 7 (1+5+1). so mean before opening we gotta close these 7 position first. so we can sell these to close long positions. so this case first row for 4 units action1= “sell”, and Action2 = “Close”. and next row is having 5 trades and we are left only opening 3 (7-4) and this trade row should be splitted in 2 row something like 3 units with Action1 =”Sell” and Action2 =”Close” and 2 Units with action1 = “Sell” and “Open”. Since we are not left with any open positon after this, Rest of the trades should be like Action1=sell and Action2=open
    5. if there is buy trade and open position is short, it should be booked on prorata basis, like for IBM (“25000”) there are 2 buy trades and one 1 short position in opening position. In this case row must be spliited into 2 -with first row must be 1 unit with action1 =buy and action2=close and other row with 1 unit and action1 =buy and action2 open.

    Current Trade:-

    Security Codes Security Name Quantiy Buy/Sell
    2210 Reliance 1 buy
    2300 Wipro 1 sell
    2410 Tesco 1 sell
    2413 TCS 4 sell
    2413 TCS 5 sell
    2413 TCS 7 sell
    2413 TCS 1 sell
    2413 TCS 1 sell
    2413 TCS 1 sell
    25000 IBM 2 buy

    Opening Position:-
    MF Securty Name Quantiy Long/Short
    2210 Reliance 1 long
    2300 Wipro 1 Short
    2413 TCS 1 buy
    2413 TCS 5 buy
    2413 TCS 1 buy
    25000 IBM -1 Short

    OutPut should be like this
    Security Codes Security Name Quantiy Buy/Sell Action1 Action2
    2210 Reliance 1 buy buy open
    2300 Wipro 1 sell sell open
    2410 Tesco 1 sell sell open
    1564 Apple 1 buy buy open
    2413 TCS 4 sell sell close
    2413 TCS 3 sell sell close
    2413 TCS 2 sell sell open
    2413 TCS 7 sell sell open
    2413 TCS 1 sell sell open
    2413 TCS 1 sell sell open
    2413 TCS 1 sell sell open
    25000 IBM 1 buy buy close
    25000 IBM 1 buy buy open

    Thanks in advance and looking forward for some possible solution.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *