Forum Discussion

UA_131's avatar
UA_131
Copper Contributor
Jun 08, 2024

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_131's avatar
      UA_131
      Copper Contributor
      Thank 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! 🙏

Resources