Background formatting from two values drawn from formulas

Posts
1
Registration date
Thursday November 1, 2018
Last seen
November 1, 2018
- - Latest reply: TrowaD
Posts
2435
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 4, 2018
- Nov 1, 2018 at 12:51 PM
Hi.
I've created a table displaying prices from suppliers. On the end two columns I've added a formula to show the header of the colomn which holds the lowest value and one which shows the lowest value from the table.

I've conditionally formatted the one which draws the header through the text formatting so every time it shows 'asda' it changes the back ground to a certain colour.

My question is how do I get the colour of the colomn which shows the value to match the one which draws the header text.
See more 

Your reply

1 reply

Posts
2435
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 4, 2018
0
Thank you
Hi Nick,

Formula's can't colour cells, so we have to use VBA.

The way the code below works is that whenever you make a change to your table of values, the formula in column O will recalculate. This will trigger the code. The only thing you have to keep in mind is to use the enter or arrow down keys to confirm your entry for the code to work as it is designed. The value in column N is then looked up in row 4 of columns A to L. Finally the colours are matched.

Here is the code:
Private Sub Worksheet_Calculate()
Dim fCell, dCell As Range

Set dCell = Range("N" & ActiveCell.Row).Offset(-1, 0)
Set fCell = Range("A4:L4").Find(dCell.Value)

fCell.Copy
dCell.PasteSpecial xlPasteFormats

Application.CutCopyMode = False
End Sub


To implement the code, right-click your sheets tab and select view code, then paste the code above in the big white field.

Is this a good solution for you?

Best regards,
Trowa
Respond to TrowaD