Forum Discussion

Vimal_Gaur's avatar
Vimal_Gaur
Brass Contributor
Jan 23, 2023
Solved

VBA Background color

VBA - Need shortcut key to change background color of ENTIRE row of active cell It should also change background color of ENTIRE rows of active / selected cells
excel
Macros and VBA
  • OliverScheurich's avatar
    OliverScheurich
    Jan 24, 2023

    Vimal_Gaur 

    Sub colorseveralwows()

Dim i As Long
Dim bereich As Range
Set bereich = Selection

For i = bereich.Row To bereich.Row + bereich.Rows.Count - 1
 
Rows(i).Interior.Color = vbBlue
 
Next i
 
End Sub

    You can run this code with the shortcut ctrl+B in the attached file. The macros colors rows 7, 8 and 9 blue if you select e.g. cells F7, F8 and F9.

Vimal_Gaur's avatar
Vimal_Gaur
Brass Contributor
Jan 25, 2023

OliverScheurich Awesome it's working.

I'll copy the code for other colors but that would be repetition of code.

Now, can you combine code for other 2 colors (red & green) with blue and refactor it.

OliverScheurich's avatar
OliverScheurich
Gold Contributor
Jan 25, 2023

Vimal_Gaur 

Sub colorseveralwowschoosecolor()

Dim entry As String
Dim i As Long
Dim bereich As Range
Set bereich = Selection

entry = InputBox("Enter the color either red, blue or green")
Select Case entry

Case Is = "blue"

For i = bereich.Row To bereich.Row + bereich.Rows.Count - 1
 
Rows(i).Interior.Color = vbBlue
 
Next i
Case Is = "red"

For i = bereich.Row To bereich.Row + bereich.Rows.Count - 1
Rows(i).Interior.Color = vbRed
 
Next i
Case Is = "green"

For i = bereich.Row To bereich.Row + bereich.Rows.Count - 1
Rows(i).Interior.Color = vbGreen
 
Next i
 
End Select
 
End Sub

You are welcome. You can try this code. In the attached file you can select rows and run the macro with the shortcut ctrl+M. In the Inputbox you can enter the color and then the rows are formatted accordingly.

Resources