Looping to Format Cells

dcoledc

Active Member
Joined
May 6, 2010
Messages
403
I am trying to change the format of cels using looping. I am new to looping and took the below code from elsewhere and tried to adapt it to fit my needs.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
 Dim rng As Range, cell As Range, org As String
    If Not Intersect(Range("F16:F21,H16:H21,O16:O19, Q16:Q19"), Target) Is Nothing Then
    
      
        For Each cell In Intersect(Range("F16:F21,H16:H21,O16:O19,Q16:Q19"), Target)
            
            Select Case cell.Column
                Case 6
                    org = 16764108
                    Set rng = Range("Y16:Y21").Offset(cell.Column - 6)
                Case 8
                    org = 5287936
                    Set rng = Range("AA16:AA21").Offset(cell.Column - 8)
                Case 15
                    org = 26367
                    Set rng = Range("AH16:AH19").Offset(cell.Column - 15)
                Case 17
                    org = 26367
                    Set rng = Range("AH16:AH19").Offset(cell.Column - 17)
            End Select
            
            If cell.Value = 1 Then
                cell.Interior.Color = org
                rng.Interior.Color = vbYellow
            Else
                cell.Interior.Color = vbWhite
                rng.Interior.Color = vbWhite
            End If
            
        Next cell
    End If
End Sub

With that code I am trying to change the fill color of a cell when I enter one into a different cell. As the code is know, it changes the fill of all the cells in the target range instead of just one. What do I need to change to make it change just one cell at a time?
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Forum statistics

Threads
1,224,603
Messages
6,179,849
Members
452,948
Latest member
UsmanAli786

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top