remove data but keep the Conditional Formatting

[TimHollis]

I have colour coded a chart according to values in cells.
So if a cell had "1" in it, it is green, and if it has "2" it is red.

Simple stuff.

I now want to delete the 1's and 2's out of the cells, but keep the colour coding.

That can't be as difficult as i am findin it ... can it?


tim

 

[DRJ]

This should do the trick. Change the sheet name to whatever the sheet is actually named.

Sub Test()

Dim Cell As Object

    Sheets("Sheet1").Range("A2:T49").FormatConditions.Delete
    For Each Cell In Sheets("Sheet1").Range("A2:T49")
        Select Case Cell.Value
            Case 1 'Color Green
                Cell.Interior.ColorIndex = 10
            Case 2 'Color Red
                Cell.Interior.ColorIndex = 3
            Case Else
        End Select
    Next
    
End Sub

 

[TimHollis]

Ok - I'll try that - before I do, do you know if this will change one I change the cell values?

i.e. does this update when a cell's value is changed?

 

[DRJ]

If you want it to run whenever a value is changed put the code in the worksheet_change section. Right click on the sheet and select view code. then from the dropdowns select worksheet, then from the second one select change.

 

[TimHollis]

Thank for your help on this.

cheers

 

[gmercer]

An easy solution is to change the font colors to the same color as the shading. Thus the number is actually there but you don't see it because its the same color as the box. Has worked before for me.

 

[JamesStraight]

I found a quick way to hide the text: use a custom number format of ""

The values remain but they are no longer visible. Easier than trying to mess with individual font colors.