Conditional Formatting Cell Color: Based on Continues value

[AGYANI]

Conditional formatting of cell in Excel 2016 only provides 3 color , Is there a way to add "Rainbow" color as the value changes based on a range values e.g

If Cell Values between
-80 and -70 = Violet
-60 and -50 = Indigo
-50 and -40 = Blue
-40 and -30 = Green
-30 and -20 = Yellow
-20 and -10 = Orange
-10 and 0 = Red

Is there a way to define the color in (R,G,B) and assign using VBA code?

Any help will be appreciated.

 

[Stevie]

Hi AGYANI,
it isn't the cleanest approach, but you could do this in VBA by detecting when the value of your cell changes, and then conditionally changing the colour.

I noticed that you missed the range -70 to -60, so I added the colour Turquoise in.

I added some code to deal with a change which affects multiple cells, as well as just a single cell, which prevents errors when you paste data, or calculate a whole sheet at once, or drag down a formula.

The below code must go in the Module for the worksheet you want to format. To do this, double click on the name of that sheet on the left in the VBA Editor and paste it in there.

Private Sub Worksheet_Change(ByVal Target As Range)
    'Stevie Chandoo.org Forums
    Dim FormatRange As Range
    Dim targetVal As Double
    Dim formatCell As Range
    Dim loopBool As Boolean
    Set FormatRange = Range("A1:A10") 'change this range to the range you want to conditionally format
    If Target.Cells.Count > 1 Then 'deal with changing more than one cell at once (paste/drag down, etc)
        loopBool = True
    Else:
        Set formatCell = Target
        GoTo singlecell:
    End If
    For Each formatCell In Target
singlecell:
        If Not Application.Intersect(FormatRange, Range(formatCell.Address)) Is Nothing Then
            targetVal = formatCell.Value
            Select Case targetVal
                Case -80 To -70
                    formatCell.Interior.Color = RGB(148, 0, 211) 'violet
                Case -70 To -60
                    formatCell.Interior.Color = RGB(75, 0, 130) 'indigo
                Case -60 To -50
                    formatCell.Interior.Color = RGB(0, 0, 255) 'blue
                Case -50 To -40
                    formatCell.Interior.Color = RGB(0, 255, 255) 'turquoise
                Case -40 To -30
                    formatCell.Interior.Color = RGB(0, 255, 0) 'green
                Case -30 To -20
                    formatCell.Interior.Color = RGB(255, 255, 0) 'yellow
                Case -20 To -10
                    formatCell.Interior.Color = RGB(255, 127, 0) 'orange=
                Case -10 To 0
                    formatCell.Interior.Color = RGB(255, 0, 0)  'red
                Case Else ' this sets the background colour to 'no fill' for any value outside your defined value range
                    formatCell.Interior.Color = xlNone
                End Select
        End If
        If Not loopBool Then GoTo singlecell2
    Next
singlecell2:
End Sub

Please let me know if this is what you had in mind.

If it was helpful, then please click 'Like'
^.^

 

[Stevie]

Hi AGYANI,
I am interested to know if this worked for you or not, please let me know if it does/does not do what you were attempting.

 

[AGYANI]

Sorry Stevie, I just saw your message, and thank for your response please hold on let me try it.. appreciate your help and thank you for adding the missing category...you are genius. I will try and post the results

 

[AGYANI]

Steve, I added the code in the "Worksheet" tab where the cell is present , my
cell value is on L1 only, so I changed the Set FormatRange = Range("L1:L1") , then I save the code , and went manually and changed the value in cell L1 to -30 or some negative value, nothing happened, seems the Private Sub Worksheet_Change(ByVal Target As Range) event is not activating when I change the value

 

[AGYANI]

I tried another method using conditional formatting where you set the "Cell Value" between ='<70' and =80, which will impact only the cell L1 , after that I used the fill color to the color I wanted in the rainbow color, I think your code is elegant to use, seems this could that the "Change Event" is not tiggering the change , I had heard Excel has problem with the "Worksheet_Change" event.

 

[Stevie]

Hi AGYANI,
apologies, I was on holiday.
Please attach a copy of your workbook and I will see why it isn't working for you, it worked fine for me.
Did you try it on the range A1:A10? If it doesn't work their either, then there is something else wrong, which will be easier for me to diagnose if I can see your workbook as like I said it works perfectly fine in testing for me.

Stevie