Excel: How to keep colors from the conditional formatting but delete values?

Question

I used conditional formatting to highlight all values above 2 in a green color and below -2 in red.  I now want to remove the values but keep the color present.  Is there a way to do this? 

I cannot simply manually highlight and use fill to do this function since I have over 6000 cells.  Automation is key here! 
Thanks

garbo7441 · Accepted Answer

Here is an event handler that will audit all rows, in all columns containing data, and set the fill color based on your criteria.

Copy the following event handler to the clipboard:


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim LastRow, LastCol
LastRow = Application.Cells. SpecialCells(xlCellTypeLastCell).Row
LastCol = Application.Cells. SpecialCells(xlCellTypeLastCell).Column
For i = 1 To LastRow
For j = 1 To LastCol
If Cells(i, j).Value > 2 And IsNumeric(Cells(i, j)) Then
Cells(i, j).Interior.ColorIndex = 4
ElseIf Cells(i, j).Value < -2 And IsNumeric(Cells(i, j)) Then
Cells(i, j).Interior.ColorIndex = 3
Else
Cells(i, j).Interior.ColorIndex = xlNone
End If
Next j
Next i
End Sub

Select the worksheet you wish to evaluate and right click the sheet tab.

Select 'View Code'.

Paste the event handler into the editing area to the right.

Close the VBE and return to the worksheet.

Double click any cell and the fill color will be applied to numeric values, as specified.  You can delete the values from any/all cells and the fill color will remain.

If you wish to re-evaluate later, simply double click again.  The fill colors will be applied to the appropriate cells, and cells with null values will have the fill color removed.

Trending News

Excel: How to keep colors from the conditional formatting but delete values?

1 Answer