+ Reply to Thread
Results 1 to 3 of 3

Identify percentages

Hybrid View

  1. #1
    Registered User
    Join Date
    02-06-2008
    Location
    Cheshire, UK
    Posts
    42

    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

  2. #2
    Forum Expert
    Join Date
    11-23-2005
    Location
    Rome
    MS-Off Ver
    Ms Office 2016
    Posts
    1,628
    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

  3. #3
    Registered User
    Join Date
    02-06-2008
    Location
    Cheshire, UK
    Posts
    42

    Smile Identify Percentages - Resolved with your help

    Hi Antonio (antoka05)

    Absolutley excellent - works a charm.

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

    Kind regards

    NoCanDo

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1