Red Dot

[Metrazal]

I have a need, using VBA, to replace certain data in a column with a red dot. In my sample document if a field in column G contains "X", "D", "B", or "E" then it is replaced by a red dot. If it contains another character then the field is cleared. My major obstacle is my inability to replace the characters with a red dot. As always, any help is appreciated.


Thanks,

Met

[jindon]

One way

Sub test()
    Application.ScreenUpdating = False
    With Range("g3", Range("g" & Rows.Count).End(xlUp))
        .FormatConditions.Delete
        .Font.ColorIndex = xlAutomatic
        .Value = Evaluate("if(isnumber(search("",""&" & .Address & _
        "&"","","",X,D,B,E,""&char(8572)&"","")),""#N/A"","""")")
        On Error Resume Next
        With .SpecialCells(2, 16)
            .Value = ChrW(9679)
            .Font.Color = vbRed
        End With
        On Error GoTo 0
    End With
    Application.ScreenUpdating = True
End Sub

[Metrazal]

Hmm. When I run the code listed above it seems to clear all the contents in column G. Let me dig into this a bit deeper, I must be missing something.

[Fluff13]

Try this mod to jindon's code

Sub test()
    Application.ScreenUpdating = False
    With Range("g3", Range("g" & Rows.Count).End(xlUp))
        .FormatConditions.Delete
        .Font.ColorIndex = xlAutomatic
        .Value = Evaluate("if(isnumber(search("",""&" & .Address & _
        "&"","","",X,D,B,E,""&"","")),""#N/A"","""")")
        On Error Resume Next
        With .SpecialCells(2, 16)
            .Value = ChrW(9679)
            .Font.Color = vbRed
        End With
        On Error GoTo 0
    End With
    Application.ScreenUpdating = True
End Sub

[jindon]

Metrazal

try change

        .Value = Evaluate("if(isnumber(search("",""&" & .Address & _
        "&"","","",X,D,B,E,""&char(8572)&"","")),""#N/A"","""")")

to

        .Value = Evaluate("if(isnumber(search("",""&" & .Address & _
        "&"","","",X,D,B,E,""&unichar(9679)&"","")),""#N/A"","""")")

[Metrazal]

Perfect. Thanks for the help. I appreciate it.

[jindon]

Did the change I suggested work?