Conditional Formatting with Drop Down Lists across multiple sheets

PlumGr8

Board Regular
Joined
Nov 23, 2017
Messages
145
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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.
 
Upvote 0
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.
 
Upvote 0
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
VBA Code:
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
VBA Code:
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.
 
Upvote 0
Solution
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.
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top