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?
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...
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
- Posts
- 4475
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- January 6, 2016
753 -So now you should have
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 Ifyou 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 SelectPrivate 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?
- Posts
- 4475
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- January 6, 2016
753 -Select Case Target
There is no "Target". There is "Cell"
Select Case Cell.Value