Color every other column within range

[always_confused]

Hello,

I have data in certain columns, and I would like to set their interior color so that every other column is green, and the others are blue using VBA. However, I only want the filled cells to be colored, not the entire column.

If I have data in Range("A1:D10"), I would like A1 and C1 to be darker green, and A2-A10 and C2-C10 to be lighter green while B1 are D1 darker blue and B2-B10 and D2-D10 are lighter blue.

I am currently doing this "manually", setting the Interior.ColorIndex for each sub range, which is super annoying. I tried doing it with a loop and columns, but I cannot figure out how to limit the number of rows to be colored to only those with data in them.

Really appreciate any help, thanks.

 

[Zot]

You can refer here

Apply color to alternate rows or columns - Microsoft Support

Highlight or shade alternate rows of data with colors or patterns to improve the readability of your Excel worksheets.

support.microsoft.com

opps, maybe not like you want

 

[always_confused]

You can refer here

Apply color to alternate rows or columns - Microsoft Support

Highlight or shade alternate rows of data with colors or patterns to improve the readability of your Excel worksheets.

support.microsoft.com

I would like to do this using VBA, do you have any recommendations for writing a Sub that'll do it?

 

[Zot]

I would like to do this using VBA, do you have any recommendations for writing a Sub that'll do it?

Are columns going to be fixed or you want to change the column from time to time?

 

[always_confused]

Are columns going to be fixed or you want to change the column from time to time?

The columns will change. I'd like to make something like Sub change_color(whatever_range_i_want_to_color As Range)

 

[Zot]

Try this:

Sub ColorSheet()

Dim n As Long
Dim colColor As Long, rngColor As Long
Dim strInput As String, rng As String
Dim ArryRow() As String, ArryCol() As String
Dim cell As Variant
Dim ws As Worksheet

Set ws = ActiveSheet

strInput = InputBox("Enter column with same color" & vbLf & "separated by comma", "ENTER COLUMN")
ArryCol = Split(strInput, ",")
Application.Dialogs(xlDialogEditColor).Show 1, 26, 82, 48
colColor = Application.ActiveWorkbook.Colors(1)

strInput = InputBox("Enter row range" & vbLf & "separated by comma", "ENTER ROW RANGE")
ArryRow = Split(strInput, ",")
Application.Dialogs(xlDialogEditColor).Show 1, 26, 82, 48
rngColor = Application.ActiveWorkbook.Colors(1)
If UBound(ArryRow) > 1 Then
    MsgBox "Only two numbers for range", vbExclamation, "WARNING"
    End
End If

For n = 0 To UBound(ArryCol)
    ws.Range(ArryCol(n) & "1").Interior.Color = colColor
    ws.Range(ArryCol(n) & ArryRow(0), ArryCol(n) & ArryRow(1)).Interior.Color = rngColor
Next

End Sub

 

[always_confused]

Try this:

Sub ColorSheet()

Dim n As Long
Dim colColor As Long, rngColor As Long
Dim strInput As String, rng As String
Dim ArryRow() As String, ArryCol() As String
Dim cell As Variant
Dim ws As Worksheet

Set ws = ActiveSheet

strInput = InputBox("Enter column with same color" & vbLf & "separated by comma", "ENTER COLUMN")
ArryCol = Split(strInput, ",")
Application.Dialogs(xlDialogEditColor).Show 1, 26, 82, 48
colColor = Application.ActiveWorkbook.Colors(1)

strInput = InputBox("Enter row range" & vbLf & "separated by comma", "ENTER ROW RANGE")
ArryRow = Split(strInput, ",")
Application.Dialogs(xlDialogEditColor).Show 1, 26, 82, 48
rngColor = Application.ActiveWorkbook.Colors(1)
If UBound(ArryRow) > 1 Then
    MsgBox "Only two numbers for range", vbExclamation, "WARNING"
    End
End If

For n = 0 To UBound(ArryCol)
    ws.Range(ArryCol(n) & "1").Interior.Color = colColor
    ws.Range(ArryCol(n) & ArryRow(0), ArryCol(n) & ArryRow(1)).Interior.Color = rngColor
Next

End Sub

Well this works well, but I'd like to just put in the whole range and have every other column colored. If I have to enter the name of every other column manually I kind of defeats the purpose....

 

[always_confused]

I figured it out in the end.

Sub color_column(color_range As Range)
Dim j As Long
Dim nb_col As Long
Dim lastrow As Long

nb_col = color_range.Columns.Count
lastrow = color_range.Range("A" & Rows.Count).End(xlUp).Row

For j = 1 To nb_col

    Range(Cells(2, j), Cells(lastrow, j)).Interior.ColorIndex = 35
    Range(Cells(1, j), Cells(1, j)).Interior.ColorIndex = 43

    If j + 1 < nb_col Then
        j = j + 1
        Range(Cells(1, j), Cells(lastrow, j)).Interior.ColorIndex = 34
        Range(Cells(1, j), Cells(1, j)).Interior.ColorIndex = 42
    End If

Next j
End Sub

 

[Zot]

I figured it out in the end.

Sub color_column(color_range As Range)
Dim j As Long
Dim nb_col As Long
Dim lastrow As Long

nb_col = color_range.Columns.Count
lastrow = color_range.Range("A" & Rows.Count).End(xlUp).Row

For j = 1 To nb_col

    Range(Cells(2, j), Cells(lastrow, j)).Interior.ColorIndex = 35
    Range(Cells(1, j), Cells(1, j)).Interior.ColorIndex = 43

    If j + 1 < nb_col Then
        j = j + 1
        Range(Cells(1, j), Cells(lastrow, j)).Interior.ColorIndex = 34
        Range(Cells(1, j), Cells(1, j)).Interior.ColorIndex = 42
    End If

Next j
End Sub

Good.

I noticed that my code might be even more tedious but I have no idea how you want to approach your task. I was thinking of what color (not to hard coded) and many other variables since you never really mentioned what step to take to achieve your goal