Highlight Cells Using VBA

[anwilson]

Mornin all.

I have a code that selects certain cells based on the user's input and highlights them yellow or gray. If possible I'd like to be able to set the cells I want highlighted equal to a range of some sort then highlight the range so the sheet isn't jumping everywhere (by selecting them) when it highlights the cells. See below for example:

sh2.Rang("B13:31").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColoIndex = xlAutomatic
.ThemeColor = xlThemeColorDarkl
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
End With

Thanks in advance!

 

[Fluff]

Try

With sh2.Range("B13:B31")
   .Pattern = xlSolid
   .PatternColoIndex = xlAutomatic
   .ThemeColor = xlThemeColorDarkl
   .TintAndShade = -0.149998474074526
   .PatternTintAndShade = 0
End With

 

[Scott T]

You do not need to select the cells to change the color.

With sh2.Range("B13:B31").Interior
    .Pattern = xlSolid
    .PatternColoIndex = xlAutomatic
    .ThemeColor = xlThemeColorDarkl
    .TintAndShade = -0.149998474074526
    .PatternTintAndShade = 0
End With

 

[anwilson]

It's giving me the error:

"Object doesn't support this property or method"

on the line defining the pattern. Any idea why?

 

[Fluff]

How about

With sh2.Range("B13:B31").Interior
   .Pattern = xlSolid
   .PatternColorIndex = xlAutomatic
   .ThemeColor = xlThemeColorDarkl
   .TintAndShade = -0.149998474074526
   .PatternTintAndShade = 0
End With

 

[anwilson]

That helped with the Pattern error, now it's giving me an error of:

"Subscript out of range"

on the ThemeColor line.

Thanks for your continued help, you are much appreciated!

 

[Scott T]

try changing it to

.ThemeColor = xlThemeColorDark1

 

[Fluff]

Try

.ThemeColor = xlThemeColorDark1

 

[anwilson]

Awesome. It works. Thank ya'll!

 

[Fluff]

You're welcome & thanks for the feedback