Condtional Formatting or VBA Code

  • Thread starter Thread starter RyanH
  • Start date Start date
R

RyanH

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
 
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
Click to expand...

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
 
Back
Top