Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel
Previous Topic Next Topic

More than 3 conditional formats in Excel

[Solved/Closed]
Report
-
 Trevin -
Hello,
I am working with an Excel spreadsheet, and need the cell background in one column to change to red if its value is within a range of numbers. Problem is, there are more than 3 ranges of numbers that I need flagged. In some cases there are many more than 3, even up to 20 or more. Can you help?


Related:

1 reply

Answer 1 / 1
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
By the power of search, using just the title of your question, i get https://ccm.net/s/More+than+3+conditional+formats+in+Excel
Report

Trouble is that macros seem to be only activated when cell values are changed. All of the values I am working with are already entered into the spreadsheet. The only way to activate the color change is to reenter the value one cell at a time, which would take way too long seeing as there are over 1000 cells per section, and up to 10 or so sections...
Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
In that case, take aquarelle macro and rename the method to some thing else. Lets say it is ConditionalFormatAfterTheFact. Also remove the reference to passed variables.

So now you should have 
Private Sub ConditionalFormatAfterTheFact() 
... 
End Sub


The second change you need to make is change this 
Set MyPlage = Range("R7:R1000")


MyPlage refered to the range where to color was to be applied. You change it to your requirement as for example 

Set MyPlage = Range("A7:F56")


Now to Run this modifed macro. Of course you need to modify the conditions. Personally I am not a big fan of a lot of IFs

So instead of 

        If Cell.Value = "Extreme" Then 
            Cell.Interior.ColorIndex = 3 
        End If 
        If Cell.Value = "Hight" Then 
            Cell.Interior.ColorIndex = 4 
        End If 

        If Cell.Value <> "Extreme" And Cell.Value <> "Hight" And Cell.Value <> "Medium" And Cell.Value <> "Low" Then 
            Cell.Interior.ColorIndex = xlNone 
        End If



you can have 

    Select Case Cell.Value 
     
        Case = "Extreme" 
            Cell.Interior.ColorIndex = 3 
         
        Case = "Hight" 
            Cell.Interior.ColorIndex = 4 
         
        Case Else 
            Cell.Interior.ColorIndex = xlNone 
    End Select
Report

I entered this exactly:

Private Sub ConditionalFormatAfterTheFact()
Set MyPlage = Range("C2:C8381")
For Each Cell In MyPlage

Select Case Target

Case 106576 To 154925
Cell.Interior.ColorIndex = 46

Case 235523 To 241668
Cell.Interior.ColorIndex = 46

Case 368764 To 397255
Cell.Interior.ColorIndex = 46

Case 413751 To 419523
Cell.Interior.ColorIndex = 46

Case 495867 To 519225
Cell.Interior.ColorIndex = 46

Case Else
Cell.Interior.ColorIndex = xlNone
End Select

Next
End Sub

and it did not work. What did I do wrong?
Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
I am presuming that you did run the macro. Here is one issue
Select Case Target

There is no "Target". There is "Cell"

Select Case Cell.Value
Report

OK, that worked. Thank you very much.
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!