Conditional formating

[John]

Hello, wondered if anyone can help? I have a macro that is suppose to be
setting up some conditional formatting. In the cell (5,6) conditional
formating is being applied as detailed below,
but after running the macro the condition is set to '=K$3=$D9" with the
other F$3 all being changed to K$3 and the $D5 changing to $D9 and $E5
chagning to $E9, can you please explain why and offer a solution ? I am
hoping to autofill once the condition works in the first cell.

' Conditional Formating for Gantt Chart
With objExcel.Cells(5, 6)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=F$3=$D5"
.FormatConditions(1).Interior.ColorIndex = 6
.FormatConditions.Add Type:=xlExpression, Formula1:="=F$3=$E5"
.FormatConditions(2).Interior.ColorIndex = 6
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(F$3>$D5,F$3<$E5)"
.FormatConditions(3).Interior.ColorIndex = 6
End With
'With objExcel
' Set objRange5 = .Cells(5, 6)
' Set objrange6 = .Range(.Cells(5, 6), .Cells(5, LastColumn))
'End With

'objRange5.AutoFill objrange6, xlFillFormats

 

[Jim Rech]

Select cell 5,6 (aka F5) before running code that enters a relative formula
like

Cells(5, 6).FormatConditions.Add Type:=xlExpression, Formula1:="=F$3=$D5"

Since the "F" and "5" do not have $ they are relative to the cell pointer at
time of creation, which in your case was at A1 rather than F5.

--
Jim
| Hello, wondered if anyone can help? I have a macro that is suppose to be
| setting up some conditional formatting. In the cell (5,6) conditional
| formating is being applied as detailed below,
| but after running the macro the condition is set to '=K$3=$D9" with the
| other F$3 all being changed to K$3 and the $D5 changing to $D9 and $E5
| chagning to $E9, can you please explain why and offer a solution ? I am
| hoping to autofill once the condition works in the first cell.
|
| ' Conditional Formating for Gantt Chart
| With objExcel.Cells(5, 6)
| .FormatConditions.Delete
| .FormatConditions.Add Type:=xlExpression, Formula1:="=F$3=$D5"
| .FormatConditions(1).Interior.ColorIndex = 6
| .FormatConditions.Add Type:=xlExpression, Formula1:="=F$3=$E5"
| .FormatConditions(2).Interior.ColorIndex = 6
| .FormatConditions.Add Type:=xlExpression, Formula1:= _
| "=AND(F$3>$D5,F$3<$E5)"
| .FormatConditions(3).Interior.ColorIndex = 6
| End With
| 'With objExcel
| ' Set objRange5 = .Cells(5, 6)
| ' Set objrange6 = .Range(.Cells(5, 6), .Cells(5, LastColumn))
| 'End With
|
| 'objRange5.AutoFill objrange6, xlFillFormats
|
|

 

[John]

Thanks Jim, all makes sense now

John