CODEWITHSUNDEEP
excelvba
Mohamed-yasir Hamir
Mohamed-yasir Hamir

Reputation: 15

Select a range of cells and colour their background

I have a project where staff are red, ambered or greened depending on their performance. The results are copied to another worksheet which lists and compiles their results.

When the staff go on holiday I want to colour the background of the cells to grey.

I wanted to select a range of cells in a row and wanted a button that I could press that would automatically colour all of those cells' backgrounds to grey.

This is what I came up with currently

Sub Macro1()
ActiveCell.CurrentRegion.Select
    For Each cell In Selection
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.249946592608417
        .PatternTintAndShade = 0      
    End With
    Next cell End Sub

I was trying to loop through a range of cells and colour each one - but only the active cell colours.

Upvotes: 0

Views: 228

Answers (2)

Mohamed-yasir Hamir
Mohamed-yasir Hamir

Reputation: 15

Sub Macro1()
    For Each cell In Selection
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.249946592608417
        .PatternTintAndShade = 0      
    End With
    Next cell End Sub

if you delete activecell.select it works aswell

Upvotes: 1

Siddharth Rout
Siddharth Rout

Reputation: 149277

So I wanted to select a range of cells in a row and wanted a button that I could press that would automatically colour all of those cells' backgrounds to grey.

If you want to just color the selected cells grey then this will be do what you want

Sub Sample()
    '~~> Check if what the user selected is a valid range
    If TypeName(Selection) <> "Range" Then
        MsgBox "Select a range first."
        Exit Sub
    End If

    Selection.Interior.ColorIndex = 48
End Sub

Upvotes: 2

Related Questions