Forum Discussion
Excel - Add value based on colour
Hi All,
I have an excel sheet where I am trying to create a cash flow estimate based on a programme of building works.
Column C has the planned week number, Column D has the planned duration in weeks and the sheet conditionally formats the cells by filling the weekly columns cells against that activity in the peach colour if its planned in for that particular week.
I'm trying to get the sheet to automate to recognising the colour and automatically adding the figure from the weekly forecasted cost from Column E. Column E is also a formula based on the total in Column F being divided by Column D. Column F is a VLOOKUP into another sheet in the same workbook. Each Row reflects a particular activity in the programme and these may have different forecasted costs. The data shown in the screenshot below is trial data.
Can an IF Statement recognise formatting? The logic is IF cell I8 is peach then add the value from E8, IF cell J8 is peach then add the value from E8, IF cell I9 is peach, then add the value from E9 and so on.
I've been trying to figure this out for days s any help would be much appreciated! Thanks 🙂
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!
- Open your MS-Excel.
- Press Alt+F11.
- Insert Module from Insert Menu.
- Paste the following code
- Use the formula SUMBYCOLOR in Excel Sheet.
Function SumByColor(SumRange As Range, SumColor As Range)
Dim SumColorValue As Integer
Dim TotalSum As Long
SumColorValue = SumColor.Interior.ColorIndex
Set rCell = SumRange
For Each rCell In SumRange
If rCell.Interior.ColorIndex = SumColorValue Then
TotalSum = TotalSum + rCell.Value
End If
Next rCell
SumByColor = TotalSum
End FunctionHi Logaraj Sekar , this unfortunately doesn't work either, I don't want to SUM any values based on colour, I just want the sheet to identify a colour has changed due to conditional formatting and then automatically input a value from a column into that coloured cell. Thanks you for your help though 🙂
- Is it possible to have a file (without sensitive data) where you explain step by step what you intend to do?
Column B represents a planned activity.
Column C is the Planned Start of the activity.
Column D is the Planned Duration.
Columns I to CE are numbered in Row 6 to represent Week Numbers 1 to 60.
When you 'plan' the activity, for say, week 1 with a 1 week duration, the sheet is conditionally formatted to fill these cells in peach.
I then want the sheet to add the Forecast Value per Week from Column 8 into the peach cells.
Hi NikolinoDE - Thank you for looking at the sheet, if you look back at it, I've amended the duration column to 4 but the formula you have created doesn't apply the value to each of the new weeks added, that is why I'm looking for the formula to read the format of the cell in order to include the value. I'm sorry to be such a pain!
SarahJP1040 Did you figure this out? I completely understand what you are trying to do and I have the exact same task I am trying to achieve. Its not highlighting based on a value, but returning a value based on highlighting.
