Streamlining Code
 

Hi All,

I would like to streamline my code & I'm having problems. Here's the
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 second
Select Case using the same code (Select Case Color2)

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

Thanks!


--
Soundman
------------------------------------------------------------------------
Soundman's Profile: http://www.excelforum.com/member.php...o&userid=24428
View this thread: http://www.excelforum.com/showthread...hreadid=389887

Soundman,

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

HTH,
Bernie
MS Excel MVP


"Soundman" wrote in message
...

Hi All,

I would like to streamline my code & I'm having problems. Here's the
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 second
Select Case using the same code (Select Case Color2)

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

Thanks!


--
Soundman
------------------------------------------------------------------------
Soundman's Profile: http://www.excelforum.com/member.php...o&userid=24428
View this thread: http://www.excelforum.com/showthread...hreadid=389887

Thank you for the help, I tend to forget about the IIF command & I can
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 to
be quick & neat.

Thanks!


--
Soundman
------------------------------------------------------------------------
Soundman's Profile: http://www.excelforum.com/member.php...o&userid=24428
View this thread: http://www.excelforum.com/showthread...hreadid=389887

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" wrote in message
...

Thank you for the help, I tend to forget about the IIF command & I can
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 to
be quick & neat.

Thanks!


--
Soundman
------------------------------------------------------------------------
Soundman's Profile: http://www.excelforum.com/member.php...o&userid=24428
View this thread: http://www.excelforum.com/showthread...hreadid=389887

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


--
Soundman
------------------------------------------------------------------------
Soundman's Profile: http://www.excelforum.com/member.php...o&userid=24428
View this thread: http://www.excelforum.com/showthread...hreadid=389887