Identify percentages

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

Try with this code:

   Range("h11").Interior.ColorIndex = xlNone
   
   If Range("h11").NumberFormat Like "*%*" Then
      myValue = Range("h11") * 100
      If myValue = 3.9 Then
         Range("h11").Interior.ColorIndex = 4
      ElseIf myValue = -3.9 Then
         Range("h11").Interior.ColorIndex = 3
      End If
   End If

Regards,
Antonio

Hi Antonio (antoka05)

Absolutley excellent - works a charm.

Many thanks for your help, I can now continue with my project.

Kind regards

NoCanDo