Conditional Formatting According to Text

Hi there

I have been reading the code for conditinal formatting, so it's posible to have more than 3 rules (see below):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer


	If Not Intersect(Target, Range("A1:A10")) is Nothing Then
		Select Case Target
			Case 1 To 5
				icolor = 6
			Case 6 To 10
				icolor = 12
			Case 11 To 15
				icolor = 7
			Case 16 To 20
				icolor = 53
			Case 21 To 25
				icolor = 15
			Case 26 To 30
				icolor = 42
			Case Else
				'Whatever
		End Select
		
		Target.Interior.ColorIndex = icolor
	End If


End Sub

This one is for numbers but I have text instead. I have different kind of text that I find with lookup. Now I want to color the cell according to the text that I find during the lookup.

Regards
Alring

Re: Conditinal formatting according to text

Hi Roy

It seems that it's only working when I write text in Range (A1:A10). But I don't write text in these cells. I make a vlookup and the result from there is written in cella A1:A10. And when I do that then the color don't change.

regards
Alring

Re: Conditinal formatting according to text

Hi Roy

Here is a small example.
A1 and A2 is without "If(isnumber)"

Alring

Re: Conditinal formatting according to text

I used isnumber because I didn't wnat to have N/A written in the cell.

Re: Conditinal formatting according to text

Hi Norie

Thats true. Is that possible??

Re: Conditinal formatting according to text

Hi Bob

If I write the code like this then it works

Private Sub Worksheet_Calculate()
    Dim icolor As Integer
    Dim MyText As String
    Dim Target As Range
     
    For Each Target In Range("A1:A10")
         
        MyText = Target.Value
        Select Case MyText
        Case "Yes"
            icolor = 6
        Case "No"
            icolor = 12
        Case "Not sure"
            icolor = 7
        Case "Don't care"
            icolor = 53
        Case "Why?"
            icolor = 15
        Case "Possibly"
            icolor = 42
        Case Else
             icolor = 2
        End Select
         
        Target.Interior.ColorIndex = icolor
    Next Target
     
End Sub

If I don't write

Case Else
             icolor = 2

Then will all the cells that is ampty under one cell that is not empty be the same color as the one above.

Alring

Re: Conditinal formatting according to text

Hi Bob

Beside that then was it a very good code you wrote.

Thanks :thanx:

Alring

Re: Conditinal formatting according to text

Hi Bob

I have found out that it's better to write

Case Else 
    icolor = none

If there is only text in cell A1 then the other cells will get the same color as A1.