Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change case...help please | Excel Worksheet Functions | |||
Using other workbooks.. | Excel Worksheet Functions | |||
Make Change Case in Excel a format rather than formula | Excel Worksheet Functions | |||
Opening a file with code without a set file name | Excel Discussion (Misc queries) | |||
Opening a file with code without a set file name | Excel Discussion (Misc queries) |