Forum Discussion
Color a cell the same as another cell's fill color
Greetings. I've searched through the Excel community for how to do this, but all I find is conditional formatting which doesn't seem to work for my case.
I have a spreadsheet with a week by week schedule. This sheet has the team numbers for each match for each week and they have specific fill colors based on external criteria such as what division the team is in along with other criteria. There is no formula that can be used to determine what color that cell should be and a team could have multiple games in the same week and the cells for that team could be different colors.
I have a second sheet which references the first sheet to print a schedule for a specific week based on what week is entered into that second sheet. The formula used to pull the team number from the first spreadsheet is as follows: INDEX(sched_north,(ROW(A4)-ROW(line_1))/5+4,week*3-1). For this discussion, the formula is really arbitrary as it just retrieves the team number from a cell in the first sheet. It could just as well be INDEX(weekly_schedule,x,y)
I am trying to pull the color from that cell from the first sheet and color the second sheet's cell with the same fill color. Is there a way to reference the color of a specific cell and use that color the same way that INDEX pulls the value of a specific cell?
5 Replies
Greetings,
Thanks for the responses. As a few of you had stated using a macro adds a level of complexity that I don't really want to get into along with having to run the macros each time an update is made. Currently, when I print off each week's schedule, I just need to change the Week name field to the current week and print the schedule. Using macros would require running the macro every time.
I did find a solution on my own. I installed the Real Statistics resource pack and used the FillColor function to populate a cell outside of the printable page for each team entry in the schedule with the color of the team number for that week and match.
Team # cell =INDEX(sched_north,$R4,$S4)
Fill Color =FillColor(INDEX(sched_north,$R4,$S4),TRUE) ; populates into T4-24, U4-24, V4-24 and W4-24
I then created conditional formating rules using a formula to apply a formatting color:
=$T4="yellow" ; apply the fill color yellow
I needed to create conditional formatting rules for each color (five different colors). I then needed to copy these five rules for each column in the schedule (four columns) referencing T4-27, U4-27, V4-27 and W4-27. This was a little cumbersome but using Format Painter to copy the rules for the columns made it quicker.
I didn't like having to polulate a cell with each fill color and reference that with the conditional formatting rules, but since I couldn't use the FillColor function in the conditional formating rules, this was the only way to get it to work.
I tried to post a few rows of the final output but got all kinds of HTML errors even when I copy pasted values before posting.
Again, thanks for the replies.
---RWR
The only way you might pull this off is by using a macro. Note you will have to adjust the cell range "D1:E2" to match yours:
Sub ColorRegionFromFormulas() Dim cl As Range Dim sourceCell As Range Application.ScreenUpdating = False For Each cl In Range("D1:E2") cl.Select 'Necessary! Application.Goto Evaluate(cl.Formula2) With cl.Interior .Color = ActiveCell.Interior.Color 'In case it isn't a themed color On Error Resume Next .Pattern = ActiveCell.Interior.Pattern .PatternColorIndex = ActiveCell.Interior.PatternColorIndex .ThemeColor = ActiveCell.Interior.ThemeColor .TintAndShade = ActiveCell.Interior.TintAndShade .PatternTintAndShade = ActiveCell.Interior.PatternTintAndShade End With Next End SubGreetings,
Thanks for the responses. As a few of you had stated, using a macro adds a level of complexity that I don't really want to get into along with having to run the macros each time an update is made. Currently, when I print off each week's schedule, I just need to change the Week name field to the current week and print the schedule. Using macros would require running the macro every time.
I did find a solution on my own. I installed the Real Statistics resource pack and used the FillColor function to populate a cell outside of the printable page for each team entry in the schedule with the color of the team number for that week and match.
Team # cell = INDEX(sched_north,$R4,$S4)
Fill Color = FillColor(INDEX(sched_north,$R4,$S4),TRUE) ; populates into T4-24, U4-24, V4-24 and W4-24
I then created conditional formating rules using a formula to apply a formatting color:
$T4="yellow" ; apply the fill color yellow
I needed to create conditional formatting rules for each color (five different colors). I then needed to copy these five rules for each column in the schedule (four columns) referencing T4-27, U4-27, V4-27 and W4-27. This was a little cumbersome but using Format Painter to copy the rules for the columns made it quicker.
I didn't like having to polulate a cell with each fill color and reference that with the conditional formatting rules, but since I couldn't use the FillColor function in the conditional formating rules, this was the only way to get it to work.
I tried to post a section of the spreadsheet in here even after copying and pasting as values, but I got a huge list of HTML errors.
Again, thanks for the replies.
---RWR
Using VBA to change formatting introduces a level of complexity, so make sure your macros are enabled when you open the workbook. Additionally, this approach will require you to run the macro whenever you want to update the colors, as it is not automatic.
Depending on your Excel settings, you may need to enable macros. Go to File > Options > Trust Center > Trust Center Settings > Macro Settings and choose the appropriate option. Remember to save your workbook as a macro-enabled workbook (.xlsm) to retain the macro code. If you encounter any issues, use breakpoints or MsgBox statements in the VBA code to help with debugging.
