Background formatting from two values drawn from formulasClosed

Question

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.

TrowaD · Answer

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

Background formatting from two values drawn from formulas

1 response

Similar discussions

Subscribe To Our Newsletter!