Mar 30 2023 03:51 AM
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 🙂
Mar 30 2023 04:24 AM
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:
This will apply conditional formatting to all cells in Column E that contain a value.
I hope this helps!
Mar 30 2023 06:20 AM
@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!
Mar 30 2023 06:46 AM
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!
Mar 30 2023 07:12 AM
@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?
Mar 30 2023 07:36 AM
Mar 30 2023 09:30 AM
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
Mar 30 2023 11:34 PM
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 🙂
Mar 30 2023 11:45 PM
Hi @NikolinoDE - I'm on Excel 365 so those colours aren't recognised and the formula won't accept the RGB code?
Mar 31 2023 12:29 AM - edited Mar 31 2023 12:54 AM
Mar 31 2023 01:07 AM
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?
Mar 31 2023 01:22 AM
Mar 31 2023 01:27 AM - edited Mar 31 2023 03:11 AM
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.
Mar 31 2023 02:26 AM
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!
Sep 12 2024 08:37 AM
@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.