Conditional Formatting with Drop Down Lists across multiple sheets

[PlumGr8]

So not sure if this is even a valid function. So in my workbook for example on sheet 1 i have a list of colors: red, blue, green, etc. On that page i have it selected as a "range" have the name defined and so on so that on the sheet where needed i can access the drop down, and i have simple conditional formatting rules to color the cell "blue" when it sees that blue was selected. But i have these same colors and lists needed across 3 separate sheets. So traditionally i would just created rules on each sheet "see blue, turn blue". But is there a to create this list only on sheet 1 with the correct colors associated to it, but it also carry the conditional rules across multiple sheets when i use the list? So if sheet 1 has the full list of color named and correctly colored, and i am on sheet 5 and select red from the drop down, is there a way to have it also show up red.

I think i can do it with Kutools? But using that isn't a valid option. So just reaching out for help, thanks

 

[severynm]

So you're looking to have ONE (identical) conditional formatting rule applied across MANY worksheets? I do not believe that is possible; you are probably best served by setting up an identical rule on each worksheet.

 

[PlumGr8]

So you're looking to have ONE (identical) conditional formatting rule applied across MANY worksheets? I do not believe that is possible; you are probably best served by setting up an identical rule on each worksheet.

Basically, someone had a way to do it before, but when i came back it was deleted. Basically on sheet one they would have a lost of colors with the correct shading etc. So on the column where the list drop down for colors was being used you just select "blue" and it would automatically shade itself blue equal to the list on the main page. I saw that it could be done with that Kutools add on, but that isn't an option.

 

[severynm]

I'm not entirely sure how the previous version was created, but you could implement a VBA solution that would do something similar. In a module, you could implement something like

Public Sub ChangeCellColor(ByRef target As Range)
    Dim color As String
    color = target.Value
    
    Select Case color
        Case "blue"
            target.Interior.color = RGB(0, 0, 255)
        Case "red"
            target.Interior.color = RGB(255, 0, 0)
        Case "green"
            target.Interior.color = RGB(0, 255, 0)
        Case Else
            target.Interior.color = RGB(255, 255, 255)
    End Select
End Sub

and then call that sub on each worksheet and pass it the cell you want to change color

Private Sub Worksheet_Change(ByVal target As Range)
    Module1.ChangeCellColor Me.Range("C6")
End Sub

Unfortunately, VBA does not have 'nice' names for setting colors like other languages do, so you'll have to go with something from this list or figure out the RGB that you want.

 

[PlumGr8]

I will try that. My experience with VBA is very limited, bigger issue is that i need to make things very "dummy" proof because several people use these sheets. But, I will try your suggestion and see how it goes thanks.