Conditional Formating

[Arcticwarrio]

Howdy ppl.
Somebody kindly pointed me to another thead which looks helpfull but i need to use text not numerics.

This is the code on the thead i was pointed to...
I have added or altered to BOLD bits

Sub CheckCells()
Set RangeToFormat = Sheets("Sheet1").Range("CellsToCheck")
For Each cell In RangeToFormat
With cell
' Empty cells
If IsEmpty(cell) Then
.Interior.ColorIndex = xlNone
' Numeric cells
ElseIf IsText(cell.Value) Then
Select Case cell.Value
Case Is = "Invoice"
.Interior.ColorIndex = 7
Case Is = "WIP"
.Interior.ColorIndex = 4
Case Is = "Stock"
.Interior.ColorIndex = 9
Case Is = "Quote"
.Interior.ColorIndex = 10
Case Is = "on hold"
.Interior.ColorIndex = 11
End Select
Else
.Interior.ColorIndex = xlNone
End If
End With
Next cell
End Sub

How do i tell it i want it to look for text?

 

[Andrew Poulsom]

VBA doesn't have an IsText function. You have to use Excel's:

ElseIf WorksheetFunction.IsText(cell.Value) Then

 

[Arcticwarrio]

nothing happens when i use the code, any ideas where i've gone wrong?
new code is...

Sub CheckCells()
Set RangeToFormat = Sheets("Sheet1").Range("E:E")
For Each cell In RangeToFormat
With cell
' Empty cells
If IsEmpty(cell) Then
.Interior.ColorIndex = xlNone
ElseIf WorksheetFunction.IsText(cell.Value) Then
Select Case cell.Value
Case Is = "Invoice"
.Interior.ColorIndex = 7
Case Is = "WIP"
.Interior.ColorIndex = 4
Case Is = "Stock"
.Interior.ColorIndex = 9
Case Is = "Quote"
.Interior.ColorIndex = 10
Case Is = "on hold"
.Interior.ColorIndex = 11
End Select
Else
.Interior.ColorIndex = xlNone
End If
End With
Next cell
End Sub

 

[Andrew Poulsom]

It worked when I tried it (although it took some time because you are checking the entire column). My test data had the following in E1:E13

Invoice
WIP
Stock
Quote
on hold
Other

Invoice
WIP
Stock
Quote
on hold
Other

 

[Arcticwarrio]

hmmm.
Where exactly did you put the cde to get it to work?
i just get black text with no fill