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?
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,802
Messages
5,833,755
Members
430,230
Latest member
Doug G

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top