Conditional formating-explaination

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

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
"John" <[email protected]> wrote in message
news:[email protected]...
| 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
|
|

Thanks Jim, all makes sense now

John
"Jim Rech" <[email protected]> wrote in message
news:[email protected]...
> 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
> "John" <[email protected]> wrote in message
> news:[email protected]...
> | 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
> |
> |
>
>