Forum Discussion
How can I change the cell colour of one cell based on either another cells' colour OR value?
Hey guys!
Working on a personal study tracker, and would really appreciate any guidance/support on this step I'm trying to setup.
Note:
- Cells from Column D12 onwards below = date
- Cells from Column E12 onwards below = my score
I would like the cells in the D and E column, e.g. D12 AND E12 to change to the same colour based on the score in column E, which are as follows:: Cell scores: 0-2.5 = Red Cells; 2.5-4 = Orange Cells, 4-5 = Green Creeks
These cells in D & E should simultaneously change to the same colour, of which there are only 3 I need: Red, Orange, Green (as you can see in: E1:G2) - if it helps, each cells' colour-index code is listed in cells: E5:G5 (in corresponding vertical order).
From looking into this, there's a few ways to do this. I can either have column D change colour by either column E's score OR cell colour. I believe the latter would work best and should be more straight-forward(?).
I'm confident in setting up the conditional formatting for column E by doing: Home>Conditional Formatting>Highlight Cell Rules>*Clicking the appropriate rule*>Input values and select background colour --- but, this only deals with Column E; Column D wouldn't immediately synchronise with this.
I've briefly researched into VBA (a whole new world!) & Conditional Fomatting>New Rule>"Use a formula... to format" -- I've been able to wrap my head around the latter, but I'm not getting the result as I know I'm messing up with the formula.
ALSO, in case it's worth noting. I have my Excel spreadsheet saved in 'XLSM' format instead of 'XLSX' as I was watching a VBA video for this and was informed to save the spreadsheet in this format. Not sure if this may impact any advice, hence the mention just in case.
I hope this makes sense! Happy to elaborate further if need ne.
Again, I really appreciate anyone whom takes it out of their time to help out. Many thanks in advance!
- UA_131Copper ContributorThank you so much for your prompt response, Sergei! This is EXACTLY what I was looking for! Cannot thank you enough my friend, this was bugging me for some time!
P.S. to anyone reading, what Sergei has done: select cells D12:D21 (date column in this example)>Home>Conditional Formatting>New Rule>"Use a Formula to determine which cells to format">Input the formula in image>Done
AND, marry this with what I mentioned earlier with the conditional formatting for column E based on cell-value, and we have a synchronised cell-colour change based on cell value!
Thank you again, Sergei! 🙏UA_131 , you are welcome and thank you for so great feedback.