# Excel conditional formating: use fill color based on a different cell's existing fill color

The following table represents the value I’m working with in Microsoft Excel.

``````+---+----+-----+---------+-----+
|   | A  |  B  |    C    |  D  |
+---+----+-----+---------+-----+
| 1 |    | Min | Current | Max |
| 2 | LF | 8   | 13      | 20  |
+---+----+-----+---------+-----+
``````

I would like the value located in the current column to change color, based on whether it’s low, medium, or high, compared to the minimum & maximum possible value.
First what I do is determine the difference between the max and minimum values; in this case d3 – a3 = 12.

From there the first 1/3, or 33% of the value 12, is the “low” range.

I was able to use Excel’s “conditional formatting” and generate the following formula under ‘New Rule’ → ‘Use a formula to determine which cells to format’:
1st rule for “low values” whose cell will be filled with a certain color; they range from 8 to 12:

``````=\$C2<ROUNDUP((\$D2-\$B2)*0.33, 0)+\$B2
``````

2nd added rule for “high values”, i.e. range from 17-20:

``````=\$C2>\$D2-ROUNDUP((\$D2-\$B2)*0.33, 0)
``````

3rd added, is for “medium values”, that don’t belong to neither high nor medium:

``````=OR(\$C2>=ROUNDUP((\$D2-\$B2)*0.33, 0)+\$B2, \$C2<=\$D2-ROUNDUP((\$D2-\$B2)*0.33, 0))
``````

I went to 3 other cells, and set each of those cells to the color I want the “low”, “medium”, and “high” ranges to be filled with.

I would like the "conditional formatting" configuration to utilize the colors, designated to one of those 3 corresponding cells, in case in the future I decide to use different colors; it will be quicker to make that transition, rather than go to each individual cells to make that color change.

• To understand better, you want to change the formatting to match a different cell based on the rule it matches? So if D2 matches rule 1, it will use the formatting from cell F1; and if you change the format in F1 you want the new format to apply to D2? – Trenly Jul 7 '18 at 6:39
• That is correct, it's the only way I can think of to make it "modular". – gimmegimme Jul 7 '18 at 12:14

If you can use VBA, I have a solution.

This tests each cell with a value in Column C against the rules. Whichever rule it matches, it copies the background color from the reference cell into the cell it is testing. It will do this any time a value is added or removed from the workbook.

``````Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
For Each Cell In Range("C3").Resize(Worksheets(1).Cells(Rows.Count, "C").End(xlUp).Row - 2, 1)
lCell = Cell.Offset(0, -1)
rCell = Cell.Offset(0, 1)
If Cell < WorksheetFunction.RoundUp((rCell - lCell) * 0.33, 0) + lCell Then
Cell.Interior.Color = Range("F3").Interior.Color
Else
If Cell > rCell - WorksheetFunction.RoundUp((rCell - lCell) * 0.33, 0) Then
Cell.Interior.Color = Range("F4").Interior.Color
Else
If WorksheetFunction.Or(Cell >= WorksheetFunction.RoundUp((rCell - lCell) * 0.33, 0) + lCell, Cell <= rCell - WorksheetFunction.RoundUp((rCell - lCell) * 0.33, 0)) Then Cell.Interior.Color = Range("F5").Interior.Color
End If
End If
Next Cell
End Sub
``````
• Thanks for the contribution. I placed the command in the 'Sheet1' (below Excel Objects folder icon) code box, and set the drop down menus to Worksheet & Change. But the code isn't executing. – gimmegimme Jul 8 '18 at 8:16
• I created it in the Workbook module. If you just paste it in the workbook module it should work. – Trenly Jul 8 '18 at 9:18

I came up with a solution that utilized both VBA, and the regular excel interface.

I had a cell, with the =SUM function, that marked all the individual cells of whose format I would like to update.

The cell (in this case C2) whose format I wanted to imitate, was called with the following:

Range("C2").Copy

From there I utilized a VBA macros algorithm that extracted the content from the cell, A30, with the collection of various cell locations I wanted to apply the conditional formatting to, in this case A30, as a string.

cellLocations = Range("A30").Formula

And then used the appropriate substring & replace functions to have it in proper layouts (change + to comma (,) and removed parentheses & =SUM) for the the following PasteSpecial function that utilizes the "Format Painter":

Range(cellLocations).PasteSpecial Paste:=xlPasteFormats

An example of the cellLocations variable in the appropriate layout is:

A2,B2,B5,C9,H8

The comma is needed as a separator/delimiter.

Any better or more straightforward solution would be appreciated.