Streamlining Code

[Soundman]

Hi All,

I would like to streamline my code & I'm having problems. Here's th
code:

Select Case Color1
Case "Blue": Selection.Interior.ColorIndex = 5
Selection.Font.ColorIndex = 2
Case "Red": Selection.Interior.ColorIndex = 3
Selection.Font.ColorIndex = 2
End Select

As it stands, the code works & there are more colors with a secon
Select Case using the same code (Select Case Color2)

First of all, I was trying to use the Set command to make a variabl
for the Selection.Interior.ColorIndex portion, but it's not working
Second, I would like to define all of the various colors once instea
of twice, which is what I'm doing now. Any ideas?

Thanks

 

[Bernie Deitrick]

Soundman,

Selection.Font.ColorIndex = 2
Selection.Interior.ColorIndex = IIf(Color1 = "Blue", 5, 3)

HTH,
Bernie
MS Excel MVP

 

[Soundman]

Thank you for the help, I tend to forget about the IIF command & I ca
see fitting that in elsewhere.

What I'm really trying to streamline are the 2 following references:
Selection.Interior.ColorIndex
Selection.Font.ColorIndex

What I was trying was:
Set Bkgd = Selection.Interior.ColorIndex
Set Fnt = Selection.Font.ColorIndex

& the matching code would read like:
Select Case Color1
Case "Blue": Bkgd = 5
Fnt = 2

I just have 20ish variables for each reference to a color & want it t
be quick & neat.

Thanks

 

[Bernie Deitrick]

Soundman,

Colorindex is a property of a class of properties (interior) of an object (Selection, which is a
range object) - you can only use set applied to objects or classes, not properties. So you could
use something like

Dim myInt As Interior
Set myInt = Selection.Interior
myInt.ColorIndex = 3

In general, if you can dim a variable As something (other than types), then you can use the Set
command with it.

HTH,
Bernie
MS Excel MVP

 

[Soundman]

That clears up a few things.
Thank you for your help