VBA to identify today's column range then apply font colour change to any cells if their equivalent in another column is of a certain value.

[PerseveringHarold]

Hi. You can try my tl;dr in bold, or find a bit more explanation beneath.

tl:dr - I think I need a script that looks at cell values in column range $E7:$E48, then goes to whichever column has today's date in U$5:HZ$5 and if, for example, $E7="Off" then Cell 7 in whichever column has today's date has to turn red (and not change back again when the date changes). And (continuing the example) if $E48="On" then Cell 48 in whichever column has today's date has to turn green.

Additional explanation:

Not sure if over-complicating this or not, but hopefully someone can help clarify my thinking here.

I have a range within a row of dates. U5 is 01/04/2023, HZ5 is 31/10/23.

Each day, I have a script that takes updated data and pastes it into whichever column has today's date as its header in Row 5. As each day passes, an archive of daily data grows across the sheet.

Once today's data has been pasted beneath the date header, I need any cells in that column, whose equivalent cell in another column (E) has a certain value, to change font colour. AND then get archived in that different colour. I've tried conditional formatting but the colour changes back to default the following day because and I can't think of how to lock the conditional format before the date changes.

Thank you.

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[PerseveringHarold]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

Thanks.

Not able to use the add-in, but here's a link to a desensitized version - Alerts For Upload.xlsx | Powered by Box

HOW THE CURRENT SHEET WORKS:
Each weekday, Column P links to a separate workbook to get updated figures.

Then I run a VBA script that copies the values from Column P, locates whichever column in the range U5:HZ5 (which act as my archive for the daily data) has today's date, then pastes the values from Column P to the cells 7 to 96 (omitting a few) below.

Columns E, F & G (and potentially H but not yet setup) then compare the figures for today (Column D, using xlookup to get data from the archive) with the previous day's figures (Column B, using xlookup) and generate alerts if the figures have crossed a threshold value (held in Column C).

WHAT I NEED THE SHEET TO DO NEXT:
If an alert appears in Columns E-H after I've run my script, I need the cell in the corresponding column for today's date in the archive to change font colour to match the alert colour.

For example - Row 12
Today's value (D12) is 0.188; Previous Day's value (B12) was 0.203. Consequently, the threshold value (C12) has been crossed and a 'REDUCE' alert has been generated in G12.

Therefore, I need the font colour in the corresponding archive cell, CQ12, to change colour from black to the same colour as the REDUCE alert (ideally, colour optional ultimately).

In the sheet I've uploaded, the colour has already changed because I've applied conditional formatting to that range, but when I open the spreadsheet tomorrow and the date has changed, that conditional formatting cannot 'save' the colour of the alert and the font reverts to black. I don't know how to get the conditional formatting to only look for whichever column in the archive has today's date and then retain the formatting forever.

Therefore I think I need a script that I can paste into my existing script so each column in the archive is only tested once.

Hope this makes sense.

Thanks.

 

[mumps]

I think that you would have to remove all the conditional formatting and use code to change the colour. The file you uploaded doesn't contain the macros you are currently using. Could you upload a version of the file which includes the existing macros?

 

[PerseveringHarold]

I found some code on the web that deletes the conditional formatting but keeps displaying the cell in the state it was in under the conditional formatting, EXCEPT...

... For reasons not clear to me, the original cell pattern is getting erased, returning just a white background instead. I'd like to retain the pattern.

Here's the code I've been trying:

Sub RemovConditionalFormattingButKeepFormat()
For Each cell In Selection
With cell
.Font.FontStyle = .DisplayFormat.Font.FontStyle
.Font.Color = .DisplayFormat.Font.Color
.Font.Strikethrough = .DisplayFormat.Font.Strikethrough
.Interior.Pattern = .DisplayFormat.Interior.Pattern
If .Interior.Pattern <> xlNone Then
.Interior.PatternColorIndex = .DisplayFormat.Interior.PatternColorIndex
.Interior.Color = .DisplayFormat.Interior.Color
End If
.Interior.TintAndShade = .DisplayFormat.Interior.TintAndShade
.Interior.PatternTintAndShade = .DisplayFormat.Interior.PatternTintAndShade
End With
Next
Selection.FormatConditions.Delete
End Sub

 

[mumps]

Please see my request in Post #4.