Set Text color to Cell color

[barnarp]

Hi all,

New to the forum and to excel macros.

I am trying to remove the numbers from conditional formatted cells and just keep the cell color.

I am not sure it can be done, but I have tried this macro which should change the text color of the cell to the same cell color of the cell in order to "hide" the text.

It doesn't work 100%, can someone please have a look at this and advise?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim LastRow, LastCol, BackgroundColor, mycell
LastRow = Application.Cells.SpecialCells(xlCellTypeLastCell).Row
LastCol = Application.Cells.SpecialCells(xlCellTypeLastCell).Column
mycell Range
For i = 1 To LastRow
For j = 1 To LastCol
If IsNumeric(Cells(i, j)) Then
BackgroundColor = Cells(i, j).Interior.ColorIndex
Cells(i, j).Font.ColorIndex = BackgroundColor
End If
Next j
Next i
End Sub

[AndyLitch]

The Cell colour set by conditional format is not the same as the cell.interior.color property.

Cells(i, j).FormatConditions(1).Interior.Color

would give you the colour of format condition 1 but NOT whether the condition is TRue or not

but you can programmatically check whether the format condition is true

[barnarp]

This code works on non formatted cells, but changed the text to WHITE and not the color of the cell?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim LastRow, LastCol, BackgroundColor, mycell
LastRow = Application.Cells.SpecialCells(xlCellTypeLastCell).Row
LastCol = Application.Cells.SpecialCells(xlCellTypeLastCell).Column
For i = 1 To LastRow
For j = 1 To LastCol
If IsNumeric(Cells(i, j)) Then
BackgroundColor = Cells(i, j).Interior.Color
Cells(i, j).Font.Color = BackgroundColor
End If
Next j
Next i
End Sub

[barnarp]

Thanks, keep getting subscript out of range...

[AndyLitch]

That code looks fine to me........

an alternative method might be to change the numberformat to make it invisible

Cells(i, j).NumberFormat = ";;;"

[barnarp]

That code looks fine to me........

an alternative method might be to change the numberformat to make it invisible

Cells(i, j).NumberFormat = ";;;"

This works great, thanks.

[AndyLitch]

Anytime bro..