Color cell background off list and then using offset

[akaitsuki]

Sub color_country()


country = Columns("C:C").Select


Select Case country
"CL" , "MX", "CO"
Case Is = ActiveCell.Interior.color = RGB(255, 0, 0)
Case Else


End Sub


Couldn't quite get this to work the way I wanted to. I have a list of countries in column C that I want the background of the cell to be red. Once that is done, if a cell in column C is red, I went the cell in column P to be a 0.

Anyone have advice? Sorry I'm really bad at this.

 

[AlphaFrog]

[color=darkblue]Sub[/color] color_country()
    
    [color=darkblue]Dim[/color] rngC [color=darkblue]As[/color] Range, country [color=darkblue]As[/color] Range
        
    [color=darkblue]Set[/color] rngC = Range("C1", Range("C" & Rows.Count).End(xlUp))
    
    [color=darkblue]For[/color] [color=darkblue]Each[/color] country [color=darkblue]In[/color] rngC
        [color=darkblue]Select[/color] [color=darkblue]Case[/color] country.Value
            [color=darkblue]Case[/color] "CL", "MX", "CO"
                country.Interior.Color = vbRed
                country.Offset(0, 13).Value = 0
            [color=darkblue]Case[/color] [color=darkblue]Else[/color]
        [color=darkblue]End[/color] [color=darkblue]Select[/color]
    [color=darkblue]Next[/color] country
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[Leith Ross]

Hello akaitsuki,

Try this...

Sub Color_Country()
    
    Dim Cell As Range
    
        For Each Cell In Range("C1", Cells(Rows.Count, "C").End(xlUp))
            Select Case Cell.Value
                Case Is = "CL", "MX", "CO"
                    Cell.Interior.Color = RGB(255, 0, 0): Cell.Offset(0, 13).Value = 0
            End Select
        Next C

End Sub

 

[Rick Rothstein]

Here is a non-looping macro that should work quite quickly for you, especially if you have a larger amount of row to process...

[table="width: 500"]
[tr]
	[td]Sub SetColumnBfromColumnA()
  Application.ScreenUpdating = False
  ActiveSheet.AutoFilterMode = False
  Rows(1).Insert
  Columns("C").AutoFilter Field:=1, Criteria1:=Array("CL", "CO", "MX"), Operator:=xlFilterValues
  On Error GoTo NothingVisible
  With Intersect(ActiveSheet.UsedRange, Columns("C").SpecialCells(xlVisible))
    .Interior.Color = vbRed
    .Offset(, 1) = 0
  End With
  ActiveSheet.AutoFilterMode = False
NothingVisible:
  Rows(1).Delete
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]

 

[akaitsuki]

You are all amazing! Thank you so much!!!