Conditional formatting and IF statements [Solved/Closed]

Report
-
 Darren -
Hi

Please can someone help with a problem I have encountered on Excel 2003. I am trying to use conditional formatting however I have 4 variables rather than 3.

I have created a spreadsheet to show my top 10 customers satisfaction levels on a monthly basis. I have created the spreadsheet to colour co-ordinate any changes that have happened from the previous month. If the score decreases then the cell will change to red, if the score stays the same then the cell will change to yellow and if the score has increased then it changes to orange. What I now want to do is have something that changes the cell to green if its a score of 100%.

The 3 rules I have in place are useful until I hit a score of 100%. If the previous month the score was 80% and it is now 100% the cell changes to orange as this was an increase from the previous month. If I then try to fill the cell green then I have to delete the 3 rules and then just fill the cell green which defeats the point of having the conditional formatting.

Is an If statement a possible solution? Any help will be greatly appreciated

Darren

2 replies

Posts
2700
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
February 25, 2021
457
Hi Darren,

Using more then 3 statements using conditional formatting is a common problem.

Try this link:
https://ccm.net/forum/affich-21716-conditional-formatting-more-than-3-in-excel

Best regards,
Trowa
2
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Sorry about all the messages.

I have now removed the conditional formatting and have used the codes you have provided and it works perfectly however I dont know what the colour code is for the lighter shade of green which is the colour I need (lime green). As the colour code you provided was a dark green. I am currently in work and having some network issues so would like to apologise if you are receiving duplicate messages from me

I also have a further question. The code you have provided I implemented it for just one cell to try and use it. I have cells that I need to use from varying areas on the spread sheet

Would I need to write 10 of these codes to allow me to do it for each cell down from the one above or is there a way of doing it to include all the 10 cells

On the sheet I am using I need to use the information to change the cell colour from cell F4 down to F13 from the information that is in cell C4 down to C13.

and also would I need to write 12 seperate codes to allow me to do it for the 12 seperate months or can I use the one code but add all the varying cell ranges

I assumed this would work but its causing problems

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("F4:F13")) Is Nothing Then
If Intersect(Target, Range("C4:C13")) Is Nothing Then Exit Sub
End If
If Range("F4:F13").Value < Range("C4:C13").Value Then Range("F4:F13").Interior.ColorIndex = 3
If Range("F4:F13").Value = Range("C4:C13").Value Then Range("F4:F13").Interior.ColorIndex = 6
If Range("F4:F13").Value > Range("C4:C13").Value Then Range("F4:F13").Interior.ColorIndex = 45
If Range("F4:F13").Value = 1 Then Range("F4:F13").Interior.ColorIndex = 10
End Sub
Posts
2700
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
February 25, 2021
457
Hi Darren,

I figured we wouldn't have reached the solution yet. But we have to start somewhere.

To figure out which colorindex matches which color, you can record a macro.
This might help you when you would like to change the color at a later time.
1. Goto top menu > Extra > Macro > record new macro.
2. A window appears, click OK. Notice another window appears to stop recording.
3. Now select a random cell and change the color.
4. Click on the stop recording button.
Congrats, you have recorded a macro.
To view the results hit Alt+F11 to open the VB Editor.
Take a look at the left side.
Double click Modules, double click on Module1.
Here you will find the colorindex number.

When I did this for Lime/Light green, I got the number 4.
If this isn't the color you meant, you now know how to get it yourself. :)

Now back to the code.
Range("F4:F13").Value won't work, cause there would be multiple values in that range.
Try this code instead:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Integer
If Intersect(Target, Range("C4:C13")) Is Nothing Then
If Intersect(Target, Range("F4:F13")) Is Nothing Then Exit Sub
End If
x = 3
Do
x = x + 1
If Range("F" & x).Value < Range("C" & x).Value Then Range("F" & x).Interior.ColorIndex = 3
If Range("F" & x).Value = Range("C" & x).Value Then Range("F" & x).Interior.ColorIndex = 6
If Range("F" & x).Value > Range("C" & x).Value Then Range("F" & x).Interior.ColorIndex = 45
If Range("F" & x).Value = 1 Then Range("F" & x).Interior.ColorIndex = 4
Loop Until x = 13
End Sub

If I'm correct the only issue remaining is the 12 months part.
If each month has a different sheet, then copy/paste the code to the other sheets.
If the 12 months appear on the same sheet, then you will have to tell me which cells are involved. Because only one "Private Sub Worksheet_Change"-code can be applied to a sheet. So the code needs to be adjusted in that case.

Do let me know if I can be of any further assistance.

Best regards,
Trowa
Hi Trowa.

That works perfectly. I have all the information on one work sheet so the cells I want to work from are quite a few. I need cells E4:E13 to change colour based on the information in B4:B13.
F4:F13 to read off C4:C13
H4:H13 to read off E4:E13
I4:I13 to read off F4:F13
K4:K13 to read off H4:H13
L4:L13 to read off I4:I13
N4:N13 to read off K4:K13
O4:O13 to read off L4:L13
B26:B35 to read off N4:N13
C26:C35 to read off O4:O13
E26:E35 to read off B26:B35
F26:F35 to read off C26:C35
H26:H35 to read off E26:E35
I26:I35 to read off F26:F35
K26:K35 to read off H26:H35
L26:L35 to read off I26:I35
N26:N35 to read off K26:K35
O26:O35 to read off L26:L35
B42:B51 to read off N26:N35
C42:C51 to read off O26:O35
E42:E51 to read off B42:B51
F42:F51 to read off C42:C51

I appreciate this is a lot of cells so apologies for this. I just dont know how I would repeat the code to cover all of this on the one sheet. If you could advise me on this I would really appreciate that
Posts
2700
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
February 25, 2021
457
Hi Darren,

That's quite a big list. But it would be harsh to stop helping now.

So here is the code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim x, y As Integer
If Intersect(Target, Range("B4:B13")) Is Nothing Then _
If Intersect(Target, Range("B26:B35")) Is Nothing Then _
If Intersect(Target, Range("B42:B51")) Is Nothing Then _
If Intersect(Target, Range("C4:C13")) Is Nothing Then _
If Intersect(Target, Range("C26:C35")) Is Nothing Then _
If Intersect(Target, Range("C42:C51")) Is Nothing Then _
If Intersect(Target, Range("E4:E13")) Is Nothing Then _
If Intersect(Target, Range("E26:E35")) Is Nothing Then _
If Intersect(Target, Range("E42:E51")) Is Nothing Then _
If Intersect(Target, Range("F4:F13")) Is Nothing Then _
If Intersect(Target, Range("F26:F35")) Is Nothing Then _
If Intersect(Target, Range("F42:F51")) Is Nothing Then _
If Intersect(Target, Range("H4:H13")) Is Nothing Then _
If Intersect(Target, Range("H26:H35")) Is Nothing Then _
If Intersect(Target, Range("I4:I13")) Is Nothing Then _
If Intersect(Target, Range("I26:I35")) Is Nothing Then _
If Intersect(Target, Range("K4:K13")) Is Nothing Then _
If Intersect(Target, Range("K26:K35")) Is Nothing Then _
If Intersect(Target, Range("L4:L13")) Is Nothing Then _
If Intersect(Target, Range("L26:L35")) Is Nothing Then _
If Intersect(Target, Range("N4:N13")) Is Nothing Then _
If Intersect(Target, Range("N26:N35")) Is Nothing Then _
If Intersect(Target, Range("O4:O13")) Is Nothing Then _
If Intersect(Target, Range("O26:O35")) Is Nothing Then Exit Sub

y = 1   'B4:B13 - E4:E13 / C4:C13 - F4:F13
            Do
            y = y + 1
            x = 3
    Do
    x = x + 1
    If Cells(x, y + 3).Value < Cells(x, y).Value Then Cells(x, y + 3).Interior.ColorIndex = 3
    If Cells(x, y + 3).Value = Cells(x, y).Value Then Cells(x, y + 3).Interior.ColorIndex = 6
    If Cells(x, y + 3).Value > Cells(x, y).Value Then Cells(x, y + 3).Interior.ColorIndex = 45
    If Cells(x, y + 3).Value = 1 Then Cells(x, y + 3).Interior.ColorIndex = 4
    Loop Until x = 13
            Loop Until y = 3

y = 4   'E4:E13 - H4:H13 / F4:F13 - I4:I13
            Do
            y = y + 1
            x = 3
    Do
    x = x + 1
    If Cells(x, y + 3).Value < Cells(x, y).Value Then Cells(x, y + 3).Interior.ColorIndex = 3
    If Cells(x, y + 3).Value = Cells(x, y).Value Then Cells(x, y + 3).Interior.ColorIndex = 6
    If Cells(x, y + 3).Value > Cells(x, y).Value Then Cells(x, y + 3).Interior.ColorIndex = 45
    If Cells(x, y + 3).Value = 1 Then Cells(x, y + 3).Interior.ColorIndex = 4
    Loop Until x = 13
            Loop Until y = 6

y = 7   'H4:H13 - K4:K13 / I4:I13 - L4:L13
            Do
            y = y + 1
            x = 3
    Do
    x = x + 1
    If Cells(x, y + 3).Value < Cells(x, y).Value Then Cells(x, y + 3).Interior.ColorIndex = 3
    If Cells(x, y + 3).Value = Cells(x, y).Value Then Cells(x, y + 3).Interior.ColorIndex = 6
    If Cells(x, y + 3).Value > Cells(x, y).Value Then Cells(x, y + 3).Interior.ColorIndex = 45
    If Cells(x, y + 3).Value = 1 Then Cells(x, y + 3).Interior.ColorIndex = 4
    Loop Until x = 13
            Loop Until y = 9

y = 10   'K4:K13 - N4:N13 / L4:L13 - O4:O13
            Do
            y = y + 1
            x = 3
    Do
    x = x + 1
    If Cells(x, y + 3).Value < Cells(x, y).Value Then Cells(x, y + 3).Interior.ColorIndex = 3
    If Cells(x, y + 3).Value = Cells(x, y).Value Then Cells(x, y + 3).Interior.ColorIndex = 6
    If Cells(x, y + 3).Value > Cells(x, y).Value Then Cells(x, y + 3).Interior.ColorIndex = 45
    If Cells(x, y + 3).Value = 1 Then Cells(x, y + 3).Interior.ColorIndex = 4
    Loop Until x = 13
            Loop Until y = 12

y = 1   'B26:B35 - N4:N13 / C26:C35 - O4:O13
            Do
            y = y + 1
            x = 3
    Do
    x = x + 1
    If Cells(x + 22, y).Value < Cells(x, y + 12).Value Then Cells(x + 22, y).Interior.ColorIndex = 3
    If Cells(x + 22, y).Value = Cells(x, y + 12).Value Then Cells(x + 22, y).Interior.ColorIndex = 6
    If Cells(x + 22, y).Value > Cells(x, y + 12).Value Then Cells(x + 22, y).Interior.ColorIndex = 45
    If Cells(x + 22, y).Value = 1 Then Cells(x + 22, y).Interior.ColorIndex = 4
    Loop Until x = 13
            Loop Until y = 3

y = 1   'B26:B35 - E26:E35 / C26:C35 - F26:F35
            Do
            y = y + 1
            x = 25
    Do
    x = x + 1
    If Cells(x, y + 3).Value < Cells(x, y).Value Then Cells(x, y + 3).Interior.ColorIndex = 3
    If Cells(x, y + 3).Value = Cells(x, y).Value Then Cells(x, y + 3).Interior.ColorIndex = 6
    If Cells(x, y + 3).Value > Cells(x, y).Value Then Cells(x, y + 3).Interior.ColorIndex = 45
    If Cells(x, y + 3).Value = 1 Then Cells(x, y + 3).Interior.ColorIndex = 4
    Loop Until x = 35
            Loop Until y = 3

y = 4   'E26:E35 - H26:H35 / F26:F35 - I26:I35
            Do
            y = y + 1
            x = 25
    Do
    x = x + 1
    If Cells(x, y + 3).Value < Cells(x, y).Value Then Cells(x, y + 3).Interior.ColorIndex = 3
    If Cells(x, y + 3).Value = Cells(x, y).Value Then Cells(x, y + 3).Interior.ColorIndex = 6
    If Cells(x, y + 3).Value > Cells(x, y).Value Then Cells(x, y + 3).Interior.ColorIndex = 45
    If Cells(x, y + 3).Value = 1 Then Cells(x, y + 3).Interior.ColorIndex = 4
    Loop Until x = 35
            Loop Until y = 6

y = 7   'H26:H35 - K26:K35 / I26:I35 - L26:L35
            Do
            y = y + 1
            x = 25
    Do
    x = x + 1
    If Cells(x, y + 3).Value < Cells(x, y).Value Then Cells(x, y + 3).Interior.ColorIndex = 3
    If Cells(x, y + 3).Value = Cells(x, y).Value Then Cells(x, y + 3).Interior.ColorIndex = 6
    If Cells(x, y + 3).Value > Cells(x, y).Value Then Cells(x, y + 3).Interior.ColorIndex = 45
    If Cells(x, y + 3).Value = 1 Then Cells(x, y + 3).Interior.ColorIndex = 4
    Loop Until x = 35
            Loop Until y = 9

y = 10   'K26:K35 - N26:N35 / L26:L35 - O26:O35
            Do
            y = y + 1
            x = 25
    Do
    x = x + 1
    If Cells(x, y + 3).Value < Cells(x, y).Value Then Cells(x, y + 3).Interior.ColorIndex = 3
    If Cells(x, y + 3).Value = Cells(x, y).Value Then Cells(x, y + 3).Interior.ColorIndex = 6
    If Cells(x, y + 3).Value > Cells(x, y).Value Then Cells(x, y + 3).Interior.ColorIndex = 45
    If Cells(x, y + 3).Value = 1 Then Cells(x, y + 3).Interior.ColorIndex = 4
    Loop Until x = 35
            Loop Until y = 12

y = 1   'B42:B51 - N26:N35 / C42:C51 - O26:O35
            Do
            y = y + 1
            x = 25
    Do
    x = x + 1
    If Cells(x + 16, y).Value < Cells(x, y + 12).Value Then Cells(x + 16, y).Interior.ColorIndex = 3
    If Cells(x + 16, y).Value = Cells(x, y + 12).Value Then Cells(x + 16, y).Interior.ColorIndex = 6
    If Cells(x + 16, y).Value > Cells(x, y + 12).Value Then Cells(x + 16, y).Interior.ColorIndex = 45
    If Cells(x + 16, y).Value = 1 Then Cells(x + 16, y).Interior.ColorIndex = 4
    Loop Until x = 35
            Loop Until y = 3

y = 1   'B42:B51 - E42:E51 / C42:C51 - F42:F51
            Do
            y = y + 1
            x = 41
    Do
    x = x + 1
    If Cells(x, y + 3).Value < Cells(x, y).Value Then Cells(x, y + 3).Interior.ColorIndex = 3
    If Cells(x, y + 3).Value = Cells(x, y).Value Then Cells(x, y + 3).Interior.ColorIndex = 6
    If Cells(x, y + 3).Value > Cells(x, y).Value Then Cells(x, y + 3).Interior.ColorIndex = 45
    If Cells(x, y + 3).Value = 1 Then Cells(x, y + 3).Interior.ColorIndex = 4
    Loop Until x = 51
            Loop Until y = 3

End Sub


Best regards,
Trowa
Hi Trowa

I cant thank you enough for all the help you have given me. That works perfectly and you have made my life so much easier. I really do appreciate all your help

Thanks again
Hi Trowa

I have put nothing in the cell with an = sign

I went to the Format tab, Conditional formatting and then just filled in the relevant boxes to allow excel to then change the cell colour based on the information I manually enter on the previous cell.

I would like to apologise if I sound extremely stupid but I really am an amateur with excel

Do I need to remove all of the conditonal formatting I have input to allow the code to work?

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!