PDA

View Full Version : Conditional Formatting with a Named Range



Mcaa51
11-07-2010, 12:00 PM
I am trying to apply conditional formatting to a range called CallTable. I would like to highlight each row in the range where the value in column k is not equal to 0.

Currently I am using the below, which is producing some odd results. For example if I highlight the CallTable range after the code has run and check the conditional formatting formula, I am seeing a different result every time with the cell location being somewhere near the bottom or the page, such as =$K65523<>0 instead of the correct =$K3<>0

Is there something that I am missing?

With wksDataDest.Range("CallTable")
.Style = "tblCall"
With .FormatConditions
.Delete
.Add Type:=xlExpression, Formula1:="=$K3<>0"
.Item(1).Interior.ColorIndex = 36
End With
End With

mdmackillop
11-07-2010, 02:48 PM
I can't replicate this. Can you post your workbook?

Bob Phillips
11-07-2010, 04:51 PM
With wksDataDest

.Range("K3").Select
With .Range("CallTable")
.Style = "tblCall"
With .FormatConditions
.Delete
.Add Type:=xlExpression, Formula1:="=$K3<>0"
.Item(1).Interior.ColorIndex = 36
End With
End With
End With

mikerickson
11-07-2010, 07:59 PM
To avoid selecting you could use either of these two techniques, one of which is commented out. I'm a bit surprised that CF doesn't take R1C1 notation directly.
Dim conditionFormula As String

With Range("CallTable")
'conditionFormula = "=$K" & .Row & "<>0"

With .FormatConditions
.Delete
.Add Type:=xlExpression, Formula1:=Application.ConvertFormula("=RC11<>0", xlR1C1, xlA1, False, Range("calltable"))
'.Add Type:=xlExpression, Formula1:=conditionFormula
.Item(1).Interior.ColorIndex = 36
End With
End With