Highlight a cell through macro

[macro_user]

hi...
i have a loop which runs through column A and column B and if the value of column A is less than the corresponding value(same row index) of column B, then i need to highlight both the cells...
Appreciate the help...

 

[Andrew Fergus]

Hi

Check out conditional formatting - this will do it for you without using a macro. Select the range of cells (assuming it starts at say A2), then select menu option Format > Conditional Formatting > change condition 1 from 'cell value is' to 'formula is' > then enter =$A2<$B2 (change the cell references to suit if you start on a row number other than 2), then pick a format that you want for 'highlighting' > Ok. That should do the trick.

HTH, Andrew

 

[schielrn]

So if A4 is less than B4 then highlight A4 and B4, then if A16 is less than B16 highlight A16 and B16 and so on? Am I understanding this correctly? If so you can do this with conditional formatting.

Select like A1:B20 or whatever your range is and go to format-->conditional formatting and then change cell value is to formula is and input this formula and color as needed:

=$A2<$B2

Hope that helps.

 

[macro_user]

No... i need it in a macro... there are 1000 rows of data which i need to check... and also i need 0 manual action... so, i have to go with a macro... is there any colour code for red or yellow which i can assign to a cell or anything similar to tat?

 

[Andrew Fergus]

Conditional formatting works on thousands of rows. It is no less manual than creating a macro once it is in place. Why do you need a macro? Is it because you don't know how many rows you need?

vbYellow and vbRed can be used.

 

[GTO]

This is a worksheet event, so it needs to be in the sheet's module that you want this on.

Right-click the sheet's tab, select View Code. Paste code.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rcell As Range
    
    If Not Application.Intersect(Target, Range("B:C")) Is Nothing _
    And Not Target.Count > 1 Then
        For Each rcell In Sheet1.Range("B1:B" & Cells(Rows.Count, 2).End(xlUp).Row)
            If rcell.Value < rcell.Offset(, 1).Value Then
                rcell.Resize(1, 2).Interior.ColorIndex = 6
            Else
                rcell.Resize(1, 2).Interior.ColorIndex = xlColorIndexNone
            
            End If
            
        Next
    End If
End Sub

Hope this helps,

Mark

 

[macro_user]

Conditional formatting works on thousands of rows. It is no less manual than creating a macro once it is in place. Why do you need a macro? Is it because you don't know how many rows you need?

vbYellow and vbRed can be used.

no... it's just tat my code is pretty big and i do a lot of processing before i do this checking and comparison and so, it would be logical to do this part also inside the macro itself...

 

[macro_user]

This is a worksheet event, so it needs to be in the sheet's module that you want this on.

Right-click the sheet's tab, select View Code. Paste code.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rcell As Range
    
    If Not Application.Intersect(Target, Range("B:C")) Is Nothing _
    And Not Target.Count > 1 Then
        For Each rcell In Sheet1.Range("B1:B" & Cells(Rows.Count, 2).End(xlUp).Row)
            If rcell.Value < rcell.Offset(, 1).Value Then
                rcell.Resize(1, 2).Interior.ColorIndex = 6
            Else
                rcell.Resize(1, 2).Interior.ColorIndex = xlColorIndexNone
            
            End If
            
        Next
    End If
End Sub

Hope this helps,

Mark

hi... i guess i have the logical part ready... just figured it out... is the below syntax correct if i use :

cells(1,"A").interior.colorindex = vbRed

would tat work? i need something to put for a cell... or is it something else?

 

[Andrew Fergus]

Ok - makes sense!

No, it has to be interior.color to use things like vbRed, vbYellow, vbBlue etc. The colorindex refers to the color number. So it would look like this:

cells(i,1).interior.color = vbRed

where i is your loop counter for each row.

Andrew

 

[schielrn]

You would need to use:

cells(1,"A").interior.color = vbRed

or:

cells(1,"A").interior.colorindex = 3

Hope that helps.