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 <Ry...@discussions.microsoft.com> 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
Format>Conditional 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" <(E-Mail Removed)> wrote in message
news:BD9CF0B2-4123-4E90-9682-(E-Mail Removed)...
>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