Highlight a cell through macro

macro_user

Board Regular
Joined
Mar 9, 2009
Messages
101
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...
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
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
 
Last edited:

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,939
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

Board Regular
Joined
Mar 9, 2009
Messages
101
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

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432

ADVERTISEMENT

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

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
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.

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

Board Regular
Joined
Mar 9, 2009
Messages
101

ADVERTISEMENT

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

Board Regular
Joined
Mar 9, 2009
Messages
101
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.

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 :
Code:
cells(1,"A").interior.colorindex = vbRed
would tat work? i need something to put for a cell... or is it something else?
 
Last edited:

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
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

Well-known Member
Joined
Apr 4, 2007
Messages
6,939
You would need to use:

Code:
cells(1,"A").interior.color = vbRed
or:
Code:
cells(1,"A").interior.colorindex = 3
Hope that helps.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,337
Messages
5,641,571
Members
417,223
Latest member
jelena_

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top