Here is an example of 4 cells in my workbook.

26  99
2   1

The workbook is coloured according to the rules 100=red 0=yellow, where things closer to 0 are more yellow and things closer to 100 are more red (This is defined by a conditional formatting rule that assigns colours based on value).

In this example 99 would be very red, 1 and 2 would be very yellow and 26 would have an orange tinge.

I want to keep the colour of the cells the same but change the values in the cells. In this example I want them to be

E=0.0,Q=26  E=0.0,Q=99
E=2.10e-11,Q=2  E=1.02e-08,Q=1

(Notice how the values on the right side of the q are the same as the first matrix)

Because of the conditional formatting rule my colours disappear when I paste new values into them.


You could always add 100 format conditions based on the value after the Q= in E=0.0,Q=99. Here's a macro that will do that for you to whatever cells you have selected, by comparing each bare cell value to 1 to 100 directly, or by comparing it to the value after the Q= if that first comparison yields an error.

You can delete the macro after you run it so that you don't have to save the file as XLSM. Uncomment the Selection.FormatConditions.Delete line by removing the apostrophe if you also want to remove the current format conditions.

This assumes that your number is always an integer between 1 and 100 (either as the bare cell value, or after the Q=). It won't work for decimal values.

Sub FormatMe()
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Dim addr As String
    addr = Replace(Selection.Cells(1).Address, "$", "")
    For i = 1 To 100
        With Selection.FormatConditions.Add(xlExpression, Formula1:= _
            "=OR(" & addr & " = " & i & ", IFERROR(RIGHT(" & addr & _
            ", LEN(" & addr & ") - FIND(""Q"", " & addr & ", 2) - 1) = """ & i & """, FALSE))")
           .Interior.Color = RGB(255, 255 - Int(i / 100 * 255), 0)
        End With
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub
  • While this does not "fix" (freeze) the last known (current) conditional color, I guess this is indeed a better solution than trying to freeze the last known color.
    – Arjan
    May 28 '17 at 13:26
  • Assumption: You want to use conditional formatting (and no VBA)
  • Assumption: The cell you want to color contains 'complex' values like "E=0.0,Q=26"

  • Answer: pick one - excel's conditional formatting is not smart enough to 'read' the value "E=0.0,Q=26" and find the number 26.

    • Using your own formatting formula could work, but that can't apply a gradient since it only does one color per condition, aka "if the number is 29 make it this exact color". (For as far as I know - please correct me)
    • Excel could automatically extract the number 26 from your cell and that (neighboring) cell could be formatted (using cell F4 =NUMBERVALUE(RIGHT(F4,LEN(F4)-FIND(",",F4)-2)))
  • My suggestion present your data in a different way, use the E and Q as headers or something and just keep every number in a separate cell.


Use paste values so only the numbers get pasted, otherwise the formatting also gets pasted.

  • It still changes the colour, it's because the cells are coloured by a formatting rule, if I manually change one of these values from 1 to 99, the value changes from yellow to red
    – Sam
    May 26 '17 at 22:32
  • @Jacob That's what conditional formatting does. If you don't want the cell color to change, remove the conditional format and set the cell fill manually, so it will not change with the value.
    – teylyn
    May 27 '17 at 8:18

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.