Hi folks

I'm struggling with trying to identify percentages so that a conditional color can be added to the cell as part of an RAG status listing.

Simply, if cell H11 contains a percentage value eg 3.9% or -3.9% (not 3.9) I would like to add a color (green for the first one, red for the second one)...

The following code works fine with values, but not with cells formatted as percentage.


Sub Colour_If()

If Range("H11") < -1 Then
   Range("W11").Select
   Selection.Interior.ColorIndex = 3 'RED
   ElseIf Range("H11") > 1 Then
          Range("W11").Select
          Selection.Interior.ColorIndex = 4 'GREEN
   Else
   Range("W11").Select
   Selection.Interior.ColorIndex = 45 'AMBER
End If
End Sub

(I cannot use conditional formatting as further down the macro, the cell will be copied and pasted, and using C.F. the formatting remains in place, likewise pasting special. However, if I use a macro to do this, then copy and paste then remove C.F. it works fine)

Have searched the forum, but sufficient to help.

Hope you can help.

Look forward to hearing from you.

NoCanDo