Excel 2007/2010 – Apply cell formats depending on cell value (VBA)

Scenario

I need a macro to check cells looking for letter. Depending on the letter, I want to assign a colour to the cell. I know how it work for one cell but how I can move the cursor to the next cells, like a loop? 

Solution

XL-Dennis provided some looping code with the proviso that  for large ranges there exist much quicker approaches than looping through a range.  Dennis also added some Error checking in case the cells contained error values.

Sub Check_Range_Value() 


Dim rnArea As Range 


Dim rnCell As Range 


Set rnArea = Range("A1:B11") 


  For Each rnCell In rnArea 


     With rnCell 


        If Not IsError(.Value) Then 


      Select Case .Value 


          Case "R" 


             .Interior.ColorIndex = 45 


          Case "Y" 


            .Interior.ColorIndex = 20 


      End Select 


       End If 


   End With 


Next 


End Sub