Let's say on the data sheet, Columns A:C are Team Name (American Football, btw), Primary color, Secondary color. The columns B and C are simply filled in cells for each of the 32 teams in the NFL. What I'd like to be able to do on the dashboard sheet is have the user select a team from data validation whose stats they want to look up, and from there have them see elements like font color, or solid color rows for section headers then flip to that team's specific color scheme.
I suppose I could make 64 conditional rules* (32teams X 2 colors), but it seems to me it would be worlds easier if the formatting performed a lookup to the data sheet to find the color as opposed to selecting the color in the conditional formatting dialogue box
*64, minus a few - more than one team likes black!
Apologies in advance if this was covered in one of the videos. I don't think I saw something like this discussed though!
Thanks!
-Kev
[Image Can Not Be Found]
Hi Kevin,
Unfortunately, there is no flexibility in the conditional formattings, colors and fonts can only be manually selected.
The only version to make the process automatic, based on user selection, is to use a personalized code, that will change the settings of that single rule as desired.
If you can provide a sample file, I can help you with a sample code.
You mentioned that columns B and C are simply filled in cells. Filled with what? a text representing the name of the color, of filled with that color, or with a number representing the color index number? (see color index, 1-56)
Cheers,
Catalin
Off-topic:
You are now part of our history, as the member with the first message posted on My Online Training Hub forum 🙂
I started with color fills. I grabbed the colors from the web
That source has colors to grab, hex or rgb codes, so anything is an option. Pretty sure I correctly attached what I was talking about. Thanks for taking a look!
-Kev
Hi Kevin,
Try the attached version.
I added the team colors into a table, for easier references in code.
The ranges where the colors should be applied are set into defined names, check the name manager to see how the ranges are set.
The code is fairly simple, based on the Worksheet_Change event, which is monitoring the changes in the dropdown cell:
If Not Intersect(Target, Range("TeamSelection")) Is Nothing Then
Dim ColorsDict As Variant, Cell As Range, Source As Worksheet
Set ColorsDict = CreateObject("Scripting.Dictionary")
Set Source = ThisWorkbook.Worksheets("references")
'load references
For Each Cell In Source.Range("ColorsTable[Team]")
ColorsDict.Add Cell.Value, Cell.Address
Next Cell
'set colors
Range("Color1").Interior.Color = Source.Range(ColorsDict(Target.Text)).Offset(0, 1).Interior.Color
Range("Color2").Interior.Color = Source.Range(ColorsDict(Target.Text)).Offset(0, 2).Interior.Color
End If
Set ColorsDict = Nothing
End Sub
Let me know if it works for you.
Cheers,
Catalin
Holy smokes! This is absolutely what I was hoping was possible.
Fantastic, thanks so much!
Best regards,
Kev
You're wellcome 🙂
Catalin Bombea said
Hi Kevin,Try the attached version.
I added the team colors into a table, for easier references in code.
The ranges where the colors should be applied are set into defined names, check the name manager to see how the ranges are set.
The code is fairly simple, based on the Worksheet_Change event, which is monitoring the changes in the dropdown cell:
Private Sub Worksheet_Change(ByVal Target As Range)<br /> If Not Intersect(Target, Range("TeamSelection")) Is Nothing Then<br /> Dim ColorsDict As Variant, Cell As Range, Source As Worksheet<br /> Set ColorsDict = CreateObject("Scripting.Dictionary")<br /> Set Source = ThisWorkbook.Worksheets("references")<br /> 'load references<br /> For Each Cell In Source.Range("ColorsTable[Team]")<br /> ColorsDict.Add Cell.Value, Cell.Address<br /> Next Cell<br /> 'set colors<br /> Range("Color1").Interior.Color = Source.Range(ColorsDict(Target.Text)).Offset(0, 1).Interior.Color<br /> Range("Color2").Interior.Color = Source.Range(ColorsDict(Target.Text)).Offset(0, 2).Interior.Color<br /> End If<br /> Set ColorsDict = Nothing<br /> End SubLet me know if it works for you.
Cheers,
Catalin
Holy Moly! I have no idea how you did this- but it's awesome. I am not an expert in Excel by any means and am taking the Expert Class now that is offered but haven't gotten to code yet. Question- I looked at the worksheet you attached and I don't see any formulas or any reference in the cells to know what you did. I don't want to sound foolish- but where do you enter the code? I would assume it's under the formulas tab, but am not sure... I am just curious on how the steps you took to come up with the finished worksheet..
Hi Kevin,
As I already said, the code is based on the Worksheet_Change event.
See this tutorial for workbook-worksheet-events-excel-vba
The code is applying the colors to the existing names Color1 and Color2, check the Name Namager to see the names and the ranges they refer to.
Hope it's more clear now 🙂
Cheers,
Catalin
