How to use a LEFT function in an IF Macro Comparison

F

Father Guido

Hi,

Essentially I have a number of possible results, such as:
Cls:A Cls:B Cls:C Cls:This Cls:That that I want to color the
entire row light green. The value I’m checking is in column E.

How do I use a left argument in a loop statement? Neither of these
work, but they should give you an idea of what I’m trying to do. If
either can be tweaked to work that would be great, but all other
suggestions are welcome. Thanks.

Norm


Range("A3").Select
Do Until ActiveCell.Value = blank
If ActiveCell.Offset(0, 4).Value = "Cls:*" Then
ActiveCell.EntireRow.Select
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
End If
ActiveCell.Offset(1, 0).Select
Loop


<OR>


Range("A3").Select
Do Until ActiveCell.Value = blank
ActiveCell.Offset(0, 4).Select
If ActiveCell.Left(4) = "Cls:*" Then
ActiveCell.EntireRow.Select
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
End If
ActiveCell.Offset(1, -4).Select
Loop
 
D

Don Guillett

try

Sub colorrowsif() ' NO selections
lr = Cells(Rows.Count, "a").End(xlUp).Row
For i = 2 To lr
If UCase(Left(Cells(i, 4), 3)) = "CLS" Then
With Rows(i).Interior.ColorIndex = 35
Next i
End Sub
 
F

Father Guido

Wow, that was quick -- what service!

Thanks Don, that looks like it'll work.

Thanks again,

Norm

On Fri, 2 Mar 2007 17:52:11 -0600, "Don Guillett"

~try
~
~Sub colorrowsif() ' NO selections
~lr = Cells(Rows.Count, "a").End(xlUp).Row
~For i = 2 To lr
~If UCase(Left(Cells(i, 4), 3)) = "CLS" Then
~With Rows(i).Interior.ColorIndex = 35
~Next i
~End Sub
 
G

Guest

Maybe something like this:

Dim l As Long

l = 3
While Range("A" & l).Value <> ""
If Left(Range("E" & l).Text, 4) = "Cls:" Then
With Range(l & ":" & l).Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
End If
l = l + 1
Wend
 
F

Father Guido

On Fri, 2 Mar 2007 15:58:01 -0800, Vergel Adriano

~Maybe something like this:
~
~ Dim l As Long
~
~ l = 3
~ While Range("A" & l).Value <> ""
~ If Left(Range("E" & l).Text, 4) = "Cls:" Then
~ With Range(l & ":" & l).Interior
~ .ColorIndex = 35
~ .Pattern = xlSolid
~ End With
~ End If
~ l = l + 1
~ Wend
~
Hi Vergel, the above does make sense to me, I will definitely
consider using it. Currently I have this...

Sub colorrowsif() ' NO selections
lr = Cells(Rows.Count, "a").End(xlUp).Row
For i = 2 To lr
If Left(Cells(i, 4), 3) = "Cls" Then
Rows(i).Interior.ColorIndex = 35
End If
ActiveCell.Offset(1, 0).Select
Next i
End Sub

Thanks,

Norm



~
~"Father Guido" wrote:
~
~> Hi,
~>
~> Essentially I have a number of possible results, such as:
~> Cls:A Cls:B Cls:C Cls:This Cls:That that I want to color the
~> entire row light green. The value I’m checking is in column E.
~>
~> How do I use a left argument in a loop statement? Neither of these
~> work, but they should give you an idea of what I’m trying to do. If
~> either can be tweaked to work that would be great, but all other
~> suggestions are welcome. Thanks.
~>
~> Norm
~>
~>
~> Range("A3").Select
~> Do Until ActiveCell.Value = blank
~> If ActiveCell.Offset(0, 4).Value = "Cls:*" Then
~> ActiveCell.EntireRow.Select
~> With Selection.Interior
~> .ColorIndex = 35
~> .Pattern = xlSolid
~> End With
~> End If
~> ActiveCell.Offset(1, 0).Select
~> Loop
~>
~>
~> <OR>
~>
~>
~> Range("A3").Select
~> Do Until ActiveCell.Value = blank
~> ActiveCell.Offset(0, 4).Select
~> If ActiveCell.Left(4) = "Cls:*" Then
~> ActiveCell.EntireRow.Select
~> With Selection.Interior
~> .ColorIndex = 35
~> .Pattern = xlSolid
~> End With
~> End If
~> ActiveCell.Offset(1, -4).Select
~> Loop
~>
~>
~>
 
G

Gary Keramidas

1. you don't need to select anything, your code will work fine with the line of
code removed
ActiveCell.Offset(1, 0).Select
the "next i" line moves the pointer to the next row for processing.
2. you also do not need the i after next:
3. you should qualify the range, too.


Sub colorrowsif() ' NO selections
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
lr = ws.Cells(Rows.Count, "a").End(xlUp).Row
For i = 2 To lr
If Left(ws.Cells(i, 4), 3) = "Cls" Then
ws.Rows(i).Interior.ColorIndex = 35
End If
Next
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Loop several sheets - not all worksheets of workbook 8
Excel hangs 1
Colour specific cell based on 2 other cell values 2
Excel VBA - If Else problem - HELP PLEASE 2
How to change the row number in macro code? 1
Scanning in reverse order 4
keep getting the error message - Compile error: Else without If 2
Format Data Series in a Pivot Chart 2

Top