Create Macro - Select Range?

[rclark88]

Hello,

I'm brand new to VBA. I created a macro so I could select a range of cells and change the background color of those cells to yellow.

The problem is that when I select a new cell range and run the macro again, there are now two cell ranges that have a yellow background color.

How would I set this up so that each time I select a new range and run the macro, the previous cell range (with yellow background) is erased. I just want to make sure that I only have one cell range with a yellow background at all times.

Here is the vba macro code:

Sub SetBackGroundColor()
'
' SetBackGroundColor Macro
' Set background color of range.
'
'
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub


Any help would be greatly appreciated.

[Bob Phillips]

Sub SetBackGroundColor()
'
' SetBackGroundColor Macro
' Set background color of range.
'
'
ActiveSheet.UsedRange.Interior.ColorIndex = xlColorIndexNone
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub

[rclark88]

Thanks. Instead of clearing the entire sheet, is there any way to just clear the previously selected range?

Can you assign the selected range to a variable dimensioned as a range?

[Bob Phillips]

You would have to have a global range variable, and set it to the selection, then next time clear it. Or use a static variable.