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,

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

HTH,
Bernie
MS Excel MVP


"Soundman" <Soundman.1sqdmv_1122303949.3684@excelforum-nospam.com> wrote in message
news:Soundman.1sqdmv_1122303949.3684@excelforum-nospam.com...
>
> 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,

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" <Soundman.1sqrik_1122321953.4414@excelforum-nospam.com> wrote in message
news:Soundman.1sqrik_1122321953.4414@excelforum-nospam.com...
>
> 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!