-1

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
0

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
0

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.

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.