Conditional colouring

[sasa]

Hi all,
I am lookink for a little help about this topic.
B1 = aaa H1 = alpha1
B2 = ccc H2 = beta
B3 = ddd H3 = gamma
B4 = eee H4 = alpha2
H5 = alpha3
well two columns. What I need is a macro that when the text in H rows is equal also partially to alpha, maybe alph, alpha, alpha1, alpha2, alpha3, and the corresponding value in B rows is equal to ccc and only in this case, the colour of this H cell changes, maybe gets red.
thanks

[JONvdHeyden]

Highlight B1 thru H1

On the menu bar go Format Conditional Formatting.

Formula is:
=AND(ISNUMBER(FIND("alph",$H1)),$B1="ccc")

Click Format > Pattern and choose red fill.

Ok

Now copy B1 thru H1 and paste the formats to the other rows.

Hope this helps.

[Bob Phillips]

Probably better to use SEARCH over FIND, it is not case-sensitive.

[JONvdHeyden]

Good spot, thanks :)

[sasa]

Pls, can I use a macro and not a formula ?
I tried to modify some code like this but I am not so good to make this.
Sub Colour_Col_B()
Dim cel As Range
Dim rng As Range
Dim LastRow As Long
Dim Col As Integer
Set rng = Range(Cells(2, 2), Cells(Rows.Count, 2).End(xlUp))
For Each cel In rng
If cel.Value = cel.Offset(0, 3) Then
cel.Interior.ColorIndex = 3
Else
End If
Next cel
End Sub

[sasa]

Anyway, there is something wrong in the formula, so excel says.

[mdmackillop]

Sub Colour_Col_B2()
Dim rng As Range
Dim Cel As Range
Set rng = Range(Cells(2, 8), Cells(Rows.Count, 8).End(xlUp))
For Each Cel In rng
If Cel.Value Like (Cel.Offset(0, 3) & "*") And Cells(Cel.Row, 2) = "ccc" Then
Cel.Interior.ColorIndex = 3
Else
Cel.Interior.ColorIndex = xlNone
End If
Next Cel
End Sub