Conditional Formating

Arcticwarrio

Active Member
Joined
Dec 6, 2005
Messages
439
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?
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
VBA doesn't have an IsText function. You have to use Excel's:

ElseIf WorksheetFunction.IsText(cell.Value) Then
 

Arcticwarrio

Active Member
Joined
Dec 6, 2005
Messages
439
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Active Member
Joined
Dec 6, 2005
Messages
439
hmmm.
Where exactly did you put the cde to get it to work?
i just get black text with no fill
 

Watch MrExcel Video

Forum statistics

Threads
1,118,530
Messages
5,572,670
Members
412,481
Latest member
nhantam
Top