Formatting Multiple Rows

[kipper19]

Looking for some help.
I have the following formula which highlights a cell ok if the number in cell A1 is greater than B1 but I'm not sure how to expand the formula for multiple rows, I have around 200 rows of data that I would like to highlight when the number in the A column is greater than its corresponding number in the B Column etc

Sub highlight()
Dim rg As Range
Dim cond1 As FormatCondition
Set rg = Range("A1", Range("A1").End(xlDown))
rg.FormatConditions.Delete
Set cond1 = rg.FormatConditions.Add(xlCellValue, xlGreater, "=$B$1")
With cond1
.Interior.Color = RGB(204, 255, 255)
End With
End Sub

 

[Joe4]

Note that your code is checking for equal to B1, not greater than:

"=$B$1"

To get it so that each cell looks at the value in column A in the same row, remove the absolute reference in front of row 1 that locks the row at 1, i.e.

"=$B1"

This will allow the row number to "float" as you go down rows, while locking the column reference in at column B.

See here for a good explanation on the different type of range references (Absolute, Relative, and Mixed):

Absolute, Relative, and Mixed Cell References in Excel

Learn all about the Absolute, Relative, and Mixed cell references in Excel and how to use these in formulas. Also, includes examples and shortcuts.

trumpexcel.com

 

[rollis13]

Change the formula in FormatConditions from Absolute to Mixed with Relative on the number of row.

from:
Set cond1 = rg.FormatConditions.Add(xlCellValue, xlGreater, "=$B$1")
to:
Set cond1 = rg.FormatConditions.Add(xlCellValue, xlGreater, "=$B1")

 

[kipper19]

Thanks Joe & Rollis, **** $ sign, works ok now

 

[rollis13]

Glad we were able to help.

 

[Joe4]

You are welcome.