Macro for more than 3 conditional formats

Closed
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello, I recently used the attached link to circumvent the limitation of excel's conditional formating (i.e. 3 conditions) and used the macro to extend conditional formatting to multiple conditions. While the macro works fine the minute I enable protection on the worksheet, the macro consistently produces "Run TIme Error 1004.. Unable to set the colorindex property of the interior class. The input cells are not protected, but they do have data validation. Any one have any ideas.




Private Sub Worksheet_Change(ByVal Target As Range)
Set MyPlage = Range("R7:R1000")
For Each Cell In MyPlage

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 = "Medium" Then
Cell.Interior.ColorIndex = 18

End If
If Cell.Value = "Low" Then
Cell.Interior.ColorIndex = 6
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

Next
End Sub

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

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Could you please upload a sample file with sample data etc on some shared site like https://authentification.site , http://wikisend.com/ , http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee.