Forum Discussion
Excel - Add value based on colour
You can use conditional formatting to set the background color of cells based on a condition.
You can then use a formula within the conditional formatting rule to set the background color of all the cells that meet that condition to a specific color.
You can create an IF statement in Column E that checks if the cell is peach-colored.
If it is, then it will add the value from Column E.
Here’s an example formula:
=IF(C2="peach",E2,"")
You can then apply conditional formatting to Column E based on whether or not it contains a value.
Here’s how you can do it:
- Select Column E
- Click on “Conditional Formatting” in the “Home” tab
- Click on “New Rule”
- Select “Use a formula to determine which cells to format”
- Enter this formula: =NOT(ISBLANK(E2))
- Click on “Format”
- Select your desired formatting
This will apply conditional formatting to all cells in Column E that contain a value.
I hope this helps!
NikolinoDE Thanks for your reply, however, your solution doesn't work with the planned weeks. I don't want to conditionally format based on value, I want to add value based on the conditional formatting if that makes sense?
What I need is for the sheet to identify if the cells are formatted in the peach colour, to add the value from column 8 - I suspect I'll need to write the formula for each row as it will need to reference column E for each row separately as the values could be different.
Thanks!
Yes, it is possible to insert a value from a specific column in Excel if a cell has a certain color.
You can achieve this using an IF-THEN formula and conditional formatting .
Here’s an example: Suppose you have a table of data in column A and want to insert the value from column B if the cell in column A is red. You can use this formula:
=IF(COLOR(A1)=3,B1,"")
This formula checks if cell A1 is red (color code 3) and returns the value from B1 if it is.
I hope that helps!
NikolinoDE - Hi again, thank you!
I think I'm getting close, one last thing, my colour code is identified as #FADACD - When I used this in the formula the sheet is returning #NAME?. I took the # out of the formula but it didn't recognise it that way either?
