Looping through Range Vs Ranges

[Aussiebear]

As I understand it, one can loop through a range, cell by cell, by using "For each cell in Range". However if you want to loop through more than one range, you need to use ".cells".

Would someone kindly step forward and provide me with the breakdown on this,please? Sort of "Looping 101", in ordinary english.

[Bob Phillips]

Let me give you a hint Ted, and see if that addresses where your mind is going.

Lookup Areas in VBA help.

[mikerickson]

As I understand it, [VBA]For Each cell in Range[/VBA] is looping through the default property of Range. (Which is .Item)
Other Properties have different defaults and the collection or array to loop through must be specified.

I can't find the list of default properties in the VBA help, but I've seen it once.

[Aussiebear]

Areas isn't where I'm trying to head Bob. I have included a workbook showing what I'm chasing.

I am using data validation to select one of four values, ( "<LOR", "<AL", ">AL", ">MRL"), but now require to apply conditional formatting, so that if the left character of the value in the cell equals ">" then the font turns red.

I had initially been thinking about using a macro to loop through the ranges, but in hindsight I might be better off just using CF.

[mdmackillop]

Why not use Conditional Formatting

Formula Is =LEFT(A1,1)=">"

[Aussiebear]

Thanks MD, I'm on it as we speak

[Simon Lloyd]

Ted i havent looked at your example but conditional formatting is the way to go =LEFT(D3,1)="<"

[Simon Lloyd]

Wow! it was crowded in that post!

[Aussiebear]

Further to the above request, I'm guessing that I'll have to use vba here, since I'll be using this on a sheet where each of the rows will be cleared and reset every so often.

[VBA]Private Sub Worksheet_SelectionChange(ByVal Target as Range)
Dim rngDataval As Range
Dim c As Range

Set Dataval = Range ( "B1:B5","D15")

For Each C in Range
If Left(C,1)=">" Then
.Font.ColorIndex = 3
End If
Next
End Sub[/VBA]

Doesn't seem right somehow?

[Simon Lloyd]

errrr....Ted theres a fair few typos there think you need to invest in option explicit! and you would be better off using the target intersect to apply your formatting.

[Aussiebear]

Back to this issue...

As this is a Worksheet_Change type event for which I already have in place to do other things required, I am looking to call this sub. Testing is for column K at this stage but eventually will be for columns K,M,O,Q,S & U.

[VBA]Sub DoResults(Target As Range)
Dim ResCell As Range
Dim rngResults As Range
' determines the initial font colour
With Range("K3:K177")
.Font.ColorIndex = xlAutomatic
End With

For Each ResCell In Range("K3:K177")
If Left(ResCell.Value, 1) = ">" Then
With Range("K3:K177")
.Font.ColorIndex = 3
End With
End If
Next
End Sub[/VBA]

Doesn't work as it errors out at line If Left(ResCell.Value, 1) = ">" Then

What is the alternative?

[Simon Lloyd]

I'm not sure what it is you want ted but this works:
[VBA]
Sub DoResults()
Dim ResCell As Range
Dim rngResults As Range
' determines the initial font colour
With Range("K3:K177")
.Font.ColorIndex = xlAutomatic
End With

For Each ResCell In Range("K3:K177")
If Left(ResCell.Value, 1) = ">" Then
ResCell.Font.ColorIndex = 3
End If
Next
End Sub
[/VBA]

[Aussiebear]

Currently I am unable to get this code of Simon's to trigger. Therefore I'm assuming it must then be in the earlier section of code.
[VBA]
Option Explicit
Option Compare Text
Const gsPassword As String = "Shona"

Private Sub Worksheet_Change(ByVal Target As Range)
Dim testFlag As Boolean
Dim ResCell As Range
Dim rngResults As Range

Me.Unprotect (gsPassword)
If Target.Cells.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
If Target.Column = 11 And Target.Row > 1 Then
'Determine the result type
If Left(ResCell.Value, 1) = ">" Then DoResults
End If
If Target.Column = 23 And Target.Row > 1 Then
'Copy data to BucketHistory and clear data from worksheet
If Target = "Cleared" Then DoClear Target

If Target = "Hold" Then DoHold Target
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
On Error Resume Next
testFlag = Evaluate(Environ("Ted Testing"))
If Not testFlag Then
Me.Protect (gsPassword)
End If
On Error GoTo 0

End Sub
Sub DoResults(ByVal Target As Range)
Dim ResCell As Range
Dim rngResults As Range
' determines the initial font colour
With Range("K3:K177")
.Font.ColorIndex = xlAutomatic
End With

For Each ResCell In Range("K3:K177")
If Left(ResCell.Value, 1) = ">" Then
ResCell.Font.ColorIndex = -16776961
End If
Next
End Sub
[/VBA]

The purpose of the sub is to change the font colour of any cell within the range K3:K177 if its value starts with ">"

[mdmackillop]

Hi Ted,
ResCell has not been set when you test it
[VBA] 'Determine the result type
If Left(ResCell.Value, 1) = ">" Then DoResults
[/VBA]

[mdmackillop]

To make your code suit the multiple column scenario

[VBA]
If Target.Row > 1 Then
Select Case Target.Column
Case 11, 13, 15, 17, 19, 21
If Left(Target, 1) = ">" Then DoResults Target.Column
Case 23
'Copy data to BucketHistory and clear data from worksheet
If Target = "Cleared" Then DoClear Target
If Target = "Hold" Then DoHold Target
End Select
End If

Sub DoResults(Col As Long)
Dim ResCell As Range
Dim rngResults As Range

Set rngResults = Range(Cells(3, Col), Cells(177, Col))
' determines the initial font colour
With rngResults
.Font.ColorIndex = xlAutomatic
End With

For Each ResCell In rngResults
If Left(ResCell.Value, 1) = ">" Then
ResCell.Font.ColorIndex = -16776961
End If
Next
End Sub

[/VBA]

[Aussiebear]

Thank you MD.