Menu
Ask a question Report

More than 3 conditional formats in Excel [Solved/Closed]

- - Latest reply:  Trevin - Jul 26, 2010 at 03:01 PM
Excel Ask a question Report
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?


See more 

1 reply

Report Respond to rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
 917
0
Thank you
By the power of search, using just the title of your question, i get http://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
rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
 917 -
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
rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
 917 -
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