PasteSpecial xlPasteFormats

[Josh O.]

I have the below code to insert a conditional format into cell H2 and then
copy it from cell h3 to the last row with data in column h. But the
resulting conditional format ends up with this formula in each non-blank cell
in column h:
=$H65325="Unsigned but Inserted"

Macro:
With Range("h2").FormatConditions
.Delete
.Add(xlExpression, , "=$h2=""Unsigned but Inserted""").Font.ColorIndex
= 3
End With
With Range("h2")
.Copy
Range("h3:h" & Range("h65000").End(xlUp).Offset(0, 0).Row).PasteSpecial
xlPasteFormats
Application.CutCopyMode = False
End With

 

[Gary Keramidas]

this is one of the times i've found it necessary to actually select a cell while
performing some action.

see if this works

With Range("h2").FormatConditions
.Delete
.Add(xlExpression, , "=$h2=""Unsigned but Inserted""").Font.ColorIndex = 3
End With
With Range("h2")
.Copy
Range("h3").Select
Range("h3:h" & Range("h65000").End(xlUp).Offset(0, 0).Row).PasteSpecial
xlPasteFormats
Application.CutCopyMode = False
End With

 

[Josh O.]

Didn't work. Same result.

this is one of the times i've found it necessary to actually select a cell while
performing some action.

see if this works

With Range("h2").FormatConditions
.Delete
.Add(xlExpression, , "=$h2=""Unsigned but Inserted""").Font.ColorIndex = 3
End With
With Range("h2")
.Copy
Range("h3").Select
Range("h3:h" & Range("h65000").End(xlUp).Offset(0, 0).Row).PasteSpecial
xlPasteFormats
Application.CutCopyMode = False
End With

 

[Gary Keramidas]

try selecting h2 first, i didn't test what i posted the first time and selected
the wrong cell. see if this works, it worked for me:

Range("h2").Select
With Range("h2").FormatConditions
.Delete
.Add(xlExpression, , "=$h2=""Unsigned but Inserted""").Font.ColorIndex = 3
End With
With Range("h2")
.Copy
Range("h3:h" & Range("h65000").End(xlUp).Offset(0, 0).Row).PasteSpecial
xlPasteFormats
Application.CutCopyMode = False
End With

 

[Josh O.]

Yes it worked that time. Thanks Gary.

try selecting h2 first, i didn't test what i posted the first time and selected
the wrong cell. see if this works, it worked for me:

Range("h2").Select
With Range("h2").FormatConditions
.Delete
.Add(xlExpression, , "=$h2=""Unsigned but Inserted""").Font.ColorIndex = 3
End With
With Range("h2")
.Copy
Range("h3:h" & Range("h65000").End(xlUp).Offset(0, 0).Row).PasteSpecial
xlPasteFormats
Application.CutCopyMode = False
End With