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.

OliverScheurich's avatar
OliverScheurich
Gold Contributor
Jan 23, 2023

Vimal_Gaur 

Sub colorrows()

Dim i As Long

i = ActiveCell.Row
Rows(i).Interior.ColorIndex = 5

End Sub

An alternative could be these lines of code. In the attached file you can select a cell and then press ctrl+k in order to color the whole selected row blue.

Vimal_Gaur's avatar
Vimal_Gaur
Brass Contributor
Jan 24, 2023

Thanks for your reply. there is much simpler way

 

Sub rowcolor()

Rows(ActiveCell.Row).Interior.Color = vbGreen

End Sub

 

above works same as your code but has a issue, plz see sheet attached.

 

Your sheet changes the background color of entire row but on first row. Suppose if I select A1,A2 & A3, then press K as per your program, it should color entire rows 1,2 & 3. Suppose if I select D5,D6 & D7, then press K as per your program, it should color entire rows 5,6 & 7.

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    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