VBA Code/Excel Formula to Change Cell Value Based on Font Color

[euge_prime2001]

Hi All,

I would like to seek your assistance if there's a way (excel formula or VBA code) that would assign those cells with value font color black to "0" and those cells with value font color red be the same or no change.

Thanks a lot for your help.

Gene

 

[onlyadrafter]

Hello,

To make this happen when you want the following code needs to go into a standard module:

Dim MY_CELL As Range
Sub font_colour()
    With ActiveSheet
        For Each MY_CELL In .UsedRange
            If MY_CELL.Font.Color = vbBlack And Not (IsEmpty(MY_CELL)) Then
                MY_CELL.Value = 0
            End If
        Next MY_CELL
    End With
End Sub

Does this work as expected?

 

[Rick Rothstein]

Here is another macro that you can consider...

[table="width: 500"]
[tr]
	[td]Sub MakeBlackFontCellsZero()
  Application.FindFormat.Clear
  Application.FindFormat.Font.Color = vbBlack
  Cells.SpecialCells(xlConstants, xlTextValues Or xlNumbers).Replace "", 0, SearchFormat:=True, ReplaceFormat:=False
  Application.FindFormat.Clear
End Sub[/td]
[/tr]
[/table]

 

[euge_prime2001]

Thanks Rick!!!
It works....