Excel - Add value based on colour

[SarahJP1040]

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 🙂 

[#2]

@SarahJP1040 

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:

1. Select Column E
2. Click on “Conditional Formatting” in the “Home” tab
3. Click on “New Rule”
4. Select “Use a formula to determine which cells to format”
5. Enter this formula: =NOT(ISBLANK(E2))
6. Click on “Format”
7. Select your desired formatting

This will apply conditional formatting to all cells in Column E that contain a value.

I hope this helps! 

[#3]

@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!

[#4]

@SarahJP1040 

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!

[#5]

@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? 

[#6]

@SarahJP1040 

Custom Number Formats - Colors

I hope this helps 🙂

[#7]

@SarahJP1040   

1. Open your MS-Excel.
2. Press Alt+F11.
3. Insert Module from Insert Menu.
4. Paste the following code
5.  
6. 
7. 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 Function

@SarahJP1040

[#8]

Hi @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 🙂

[#9]

Hi @NikolinoDE - I'm on Excel 365 so those colours aren't recognised and the formula won't accept the RGB code? 

[#10]

@SarahJP1040 

ColorIndex

Color Palette and the 56 Excel ColorIndex Colors

Excels 56 ColorIndex Colors

[#11]

Hi @SarahJP1040 

I'm not quite sure what you are trying to achieve here. But why not use your conditions in formatting the cells to peach?

[#12]

Is it possible to have a file (without sensitive data) where you explain step by step what you intend to do?

[#13]

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. 

[#14]

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! 

[#15]

@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.