PasteSpecial xlPasteFormats

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
Didn't work. Same result.

Gary Keramidas said:
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
Click to expand...
 
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
 
Yes it worked that time. Thanks Gary.

Gary Keramidas said:
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
Click to expand...
 
Back
Top