conditional formatting help

[tkaplan]

Hi all,

So I have a worksheet where columns a & b are row headers. column c is data i'd like to compare to. so for columns e thru however many columns there are, from rows 3 thru 34, i want to compare the data to the data in column c and if it's different then make it bold and red.
i'm at the point in my macro where cell d1 is the active cell. here's what i have:


If plansFormatted > 1 Then '(this is because there are other formats happening to column c first and i dont want this to run on column c)
Range(ActiveCell.Offset(2, 0), ActiveCell.Offset(33, 0)).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=D3<>$C3" '(I know that this is where my problem lies - i just dont know how to fix it)
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With
ActiveCell.Offset(-2, 0).Activate
End If

ActiveCell.Offset(0, 1).Select

then i have this repeating for however many columns I have which I figured out earlier.
So this works for column D. the problem is that the "=D3<>$C3" doesnt change to E3 for column E, etc. I just dont know how to say "= whatever the column currently is and row currently is" etc.

any help would be really appreciated!

thanks!

[Bob Phillips]

If plansFormatted > 1 Then '(this is because there are other formats happening to _
column c first and i dont want this to run on column c)

LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
With ActiveCell.Offset(2, 0).Resize(32, LastCol - 3)

.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=D3<>$C3"
With .FormatConditions(1).Font

.Bold = True
.Italic = False
.ColorIndex = 3
End With
End With
End If

[tkaplan]

so this really does all the columns at the same time. in this situation it should work for me. but just for personal knowledge, is there a way to do it to make that "d3" a variable?

thanks!

[Bob Phillips]

You mean like



If plansFormatted > 1 Then '(this is because there are other formats happening to _
column c first And i dont want this To run on column c)

LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
With ActiveCell.Offset(2, 0).Resize(32, LastCol - 3)

.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=" & ActiveCell.Offset(2, 0).Address(False, False) & "<>$C3"
With .FormatConditions(1).Font

.Bold = True
.Italic = False
.ColorIndex = 3
End With
End With
End If