Conditional formating
 

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

Conditional formating
 

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" wrote in message
...
| 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
|
|

Conditional formating
 

Thanks Jim, all makes sense now

John
"Jim Rech" wrote in message
...
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" wrote in message
...
| 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
|
|