VBA: Change text colour to red

[harky]

Hi,
I need a function not macro.

to change Column I , ERROR! to red.

e.g

ERROR! Pls check the file
ERROR! Pls check the folder

I know can use conditional formulas but conditional dont found at older excel format.
SO i like to use vba

 

[harky]

Public Sub changeColor() 

Set wks = Worksheets("sheet1")
wks.Range("I" & i)
.Characters.Text = "ERROR!"
Font.ColorIndex = 3 

End Sub

 

[Fluff]

How about

Sub harky()
   Dim Cl As Range
   Dim x As Long
   With Sheets("Sheet1")
      For Each Cl In .Range("I1", .Range("I" & Rows.Count).End(xlUp))
         x = InStr(1, Cl, "ERROR", vbTextCompare)
         If x > 0 Then Cl.Characters(x, 5).Font.Color = vbRed
      Next Cl
   End With
End Sub

 

[harky]

This work great but possible not as MACRO?

As long i open the excel it auto highlighted it?

How about

Sub harky()
   Dim Cl As Range
   Dim x As Long
   With Sheets("Sheet1")
      For Each Cl In .Range("I1", .Range("I" & Rows.Count).End(xlUp))
         x = InStr(1, Cl, "ERROR", vbTextCompare)
         If x > 0 Then Cl.Characters(x, 5).Font.Color = vbRed
      Next Cl
   End With
End Sub

 

[Fluff]

Sorry but I don't understand what you mean.

 

[harky]

Automatically run a macro when opening a excel

I try to set Private Sub but it dont works

Sorry but I don't understand what you mean.

 

[Fluff]

In that case use

Private Sub Workbook_Open()
   Dim Cl As Range
   Dim x As Long
   With Sheets("Sheet1")
      For Each Cl In .Range("I1", .Range("I" & Rows.Count).End(xlUp))
         x = InStr(1, Cl, "ERROR", vbTextCompare)
         If x > 0 Then Cl.Characters(x, 5).Font.Color = vbRed
      Next Cl
   End With
End Sub

and put it in the ThisWorkbook module

 

[harky]

ah.. cool. this work great..
Thank u!!!!!!!

In that case use

Private Sub Workbook_Open()
   Dim Cl As Range
   Dim x As Long
   With Sheets("Sheet1")
      For Each Cl In .Range("I1", .Range("I" & Rows.Count).End(xlUp))
         x = InStr(1, Cl, "ERROR", vbTextCompare)
         If x > 0 Then Cl.Characters(x, 5).Font.Color = vbRed
      Next Cl
   End With
End Sub

and put it in the ThisWorkbook module

 

[Fluff]

You're welcome & thanks for the feedback

 

[harky]

Hi, i just notice the code,

If my column was highlighted in black at the beginning. it will show this BUT
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD="align: center"]Status[/TD]
[/TR]
[TR]
[TD]ERROR Pls check the file[/TD]
[/TR]
</tbody>[/TABLE]



If i re-activate my exiting MACRO and re-fill the column again. It will highlight the whole text in red.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD="align: center"]Status[/TD]
[/TR]
[TR]
[TD]ERROR Pls check the file[/TD]
[/TR]
</tbody>[/TABLE]

You're welcome & thanks for the feedback