I want to conditionally format an entire column. Each cell in the column may
equal "Ready", "Archive", "Late-1", "Late-2", "Late-3", and so on. I want
all the cells that contain "Late" in them to have an interior colorindex = 3.
I think I need to return the first 4 letters of the cell in this condition.
How do I do that?

This does not work, because I do not know how to represent Cell.Value in
this formula below:
Cell Value Is - Equal To - =LEFT(Cell.Value, 4)="Late"

I would consider VBA code if it is really fast, the column may contain about
400 cells to scan down. I have this, but I would like a faster way to code
it.

For i = 3 To Cells(Rows.Count, "A").End(xlUp).Row
If Left(Cells(i, "L", 4)) = "Late" Then
Cells(i, "L").Interior.ColorIndex = 3
End If
Next i

--
Cheers,
Ryan

On Sep 10, 3:31*pm, RyanH wrote:
I want to conditionally format an entire column. *Each cell in the column may
equal "Ready", "Archive", "Late-1", "Late-2", "Late-3", and so on. *I want
all the cells that contain "Late" in them to have an interior colorindex = 3.
*I think I need to return the first 4 letters of the cell in this condition. *
How do I do that?

This does not work, because I do not know how to represent Cell.Value in
this formula below:
Cell Value Is - Equal To - =LEFT(Cell.Value, 4)="Late"

I would consider VBA code if it is really fast, the column may contain about
400 cells to scan down. *I have this, but I would like a faster way to code
it.

* * For i = 3 To Cells(Rows.Count, "A").End(xlUp).Row
* * * * If Left(Cells(i, "L", 4)) = "Late" Then
* * * * * * Cells(i, "L").Interior.ColorIndex = 3
* * * * End If
* * Next i

--
Cheers,
Ryan

Use conditional formatting -

Select column L
FormatConditional Formatting
Formula is =LEFT(L1,4)="Late"
Pattern = select the colour of your choice

That will do it for you.


S


That should do it for you.

maybe this can get you started:

Sub test()
Range("L1").Select
With Columns("L")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=upper(LEFT($L1,4))=""LATE"""
.FormatConditions(1).Interior.ColorIndex = 3
End With
End Sub


--


Gary


"RyanH" wrote in message
...
I want to conditionally format an entire column. Each cell in the column may
equal "Ready", "Archive", "Late-1", "Late-2", "Late-3", and so on. I want
all the cells that contain "Late" in them to have an interior colorindex = 3.
I think I need to return the first 4 letters of the cell in this condition.
How do I do that?

This does not work, because I do not know how to represent Cell.Value in
this formula below:
Cell Value Is - Equal To - =LEFT(Cell.Value, 4)="Late"

I would consider VBA code if it is really fast, the column may contain about
400 cells to scan down. I have this, but I would like a faster way to code
it.

For i = 3 To Cells(Rows.Count, "A").End(xlUp).Row
If Left(Cells(i, "L", 4)) = "Late" Then
Cells(i, "L").Interior.ColorIndex = 3
End If
Next i

--
Cheers,
Ryan