| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Format with VBA - Interior Colour of cell based on value from in-cell dropdown
hi all,
new to VBA - any help greatly appreciated I have searched high and low, possibly not asking the right questions however each cell in my range (D3:AH17) has a Data Validation drop down box. The dropdown is the same in every cell and contains seven values, SL, RL, RDO, TD, PH, SPL, SL (refering to types of leave etc) I need to conditional format the cell (change the interior colour) depending on what the user selects from the dropdown, or leave the interior colour as default if the cell remains empty. I have more than 3 criteria and hence need some VBA code... Im guessing a ThisWorkbook Change event? but im only guessing!! can someone help me out? regards steve |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Format with VBA - Interior Colour of cell based on value from in-cell dropdown
Hi
below a repost ------ Hi conditional format only accepts 3 conditions though you have a fourth if you include the default format. If you only want to apply different FONT colors based on NUMBERS, you can define up to 6 different styles. See: http://www.mcgimpsey.com/excel/conditional6.html for instructions how to do it For everything else you'll need VBA code (e.g. process the worksheet_change event and apply your format based on the cell values). The following will color the entry in cell A1:A100 based on its value: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("A1:A100")) Is Nothing Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False With Target Select Case .Value Case "Red": .Interior.ColorIndex = 3 Case "Blue": .Interior.ColorIndex = 10 'etc. End Select End With CleanUp: Application.EnableEvents = True End Sub -- Regards Frank Kabel Frankfurt, Germany Steve wrote: hi all, new to VBA - any help greatly appreciated I have searched high and low, possibly not asking the right questions however each cell in my range (D3:AH17) has a Data Validation drop down box. The dropdown is the same in every cell and contains seven values, SL, RL, RDO, TD, PH, SPL, SL (refering to types of leave etc) I need to conditional format the cell (change the interior colour) depending on what the user selects from the dropdown, or leave the interior colour as default if the cell remains empty. I have more than 3 criteria and hence need some VBA code... Im guessing a ThisWorkbook Change event? but im only guessing!! can someone help me out? regards steve |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Format with VBA - Interior Colour of cell based on value from in-cell dropdown
thanks frank!! however for some reason making a selection from the incell dropdown list does not fire the worksheet change event? I need to double click inside the actual cell and then click outside again to make excel realise the cell's value has changed... any ideas? Frank Kabel wrote in message ... Hi below a repost ------ Hi conditional format only accepts 3 conditions though you have a fourth if you include the default format. If you only want to apply different FONT colors based on NUMBERS, you can define up to 6 different styles. See: http://www.mcgimpsey.com/excel/conditional6.html for instructions how to do it For everything else you'll need VBA code (e.g. process the worksheet_change event and apply your format based on the cell values). The following will color the entry in cell A1:A100 based on its value: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("A1:A100")) Is Nothing Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False With Target Select Case .Value Case "Red": .Interior.ColorIndex = 3 Case "Blue": .Interior.ColorIndex = 10 'etc. End Select End With CleanUp: Application.EnableEvents = True End Sub -- Regards Frank Kabel Frankfurt, Germany Steve wrote: hi all, new to VBA - any help greatly appreciated I have searched high and low, possibly not asking the right questions however each cell in my range (D3:AH17) has a Data Validation drop down box. The dropdown is the same in every cell and contains seven values, SL, RL, RDO, TD, PH, SPL, SL (refering to types of leave etc) I need to conditional format the cell (change the interior colour) depending on what the user selects from the dropdown, or leave the interior colour as default if the cell remains empty. I have more than 3 criteria and hence need some VBA code... Im guessing a ThisWorkbook Change event? but im only guessing!! can someone help me out? regards steve |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Format with VBA - Interior Colour of cell based on value from in-cell dropdown
Hi
are you using Excel 97? -- Regards Frank Kabel Frankfurt, Germany Steve wrote: thanks frank!! however for some reason making a selection from the incell dropdown list does not fire the worksheet change event? I need to double click inside the actual cell and then click outside again to make excel realise the cell's value has changed... any ideas? Frank Kabel wrote in message ... Hi below a repost ------ Hi conditional format only accepts 3 conditions though you have a fourth if you include the default format. If you only want to apply different FONT colors based on NUMBERS, you can define up to 6 different styles. See: http://www.mcgimpsey.com/excel/conditional6.html for instructions how to do it For everything else you'll need VBA code (e.g. process the worksheet_change event and apply your format based on the cell values). The following will color the entry in cell A1:A100 based on its value: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("A1:A100")) Is Nothing Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False With Target Select Case .Value Case "Red": .Interior.ColorIndex = 3 Case "Blue": .Interior.ColorIndex = 10 'etc. End Select End With CleanUp: Application.EnableEvents = True End Sub -- Regards Frank Kabel Frankfurt, Germany Steve wrote: hi all, new to VBA - any help greatly appreciated I have searched high and low, possibly not asking the right questions however each cell in my range (D3:AH17) has a Data Validation drop down box. The dropdown is the same in every cell and contains seven values, SL, RL, RDO, TD, PH, SPL, SL (refering to types of leave etc) I need to conditional format the cell (change the interior colour) depending on what the user selects from the dropdown, or leave the interior colour as default if the cell remains empty. I have more than 3 criteria and hence need some VBA code... Im guessing a ThisWorkbook Change event? but im only guessing!! can someone help me out? regards steve |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Format with VBA - Interior Colour of cell based on value from in-cell dropdown
yes (unfortunately)
i think 2000 and above is different - but in 97 the worksheet change event does not execute after a selection from a incell dropdown list?? trying to think of a work around but my imagination is failing me.... Frank Kabel wrote in message ... Hi are you using Excel 97? -- Regards Frank Kabel Frankfurt, Germany Steve wrote: thanks frank!! however for some reason making a selection from the incell dropdown list does not fire the worksheet change event? I need to double click inside the actual cell and then click outside again to make excel realise the cell's value has changed... any ideas? Frank Kabel wrote in message ... Hi below a repost ------ Hi conditional format only accepts 3 conditions though you have a fourth if you include the default format. If you only want to apply different FONT colors based on NUMBERS, you can define up to 6 different styles. See: http://www.mcgimpsey.com/excel/conditional6.html for instructions how to do it For everything else you'll need VBA code (e.g. process the worksheet_change event and apply your format based on the cell values). The following will color the entry in cell A1:A100 based on its value: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("A1:A100")) Is Nothing Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False With Target Select Case .Value Case "Red": .Interior.ColorIndex = 3 Case "Blue": .Interior.ColorIndex = 10 'etc. End Select End With CleanUp: Application.EnableEvents = True End Sub -- Regards Frank Kabel Frankfurt, Germany Steve wrote: hi all, new to VBA - any help greatly appreciated I have searched high and low, possibly not asking the right questions however each cell in my range (D3:AH17) has a Data Validation drop down box. The dropdown is the same in every cell and contains seven values, SL, RL, RDO, TD, PH, SPL, SL (refering to types of leave etc) I need to conditional format the cell (change the interior colour) depending on what the user selects from the dropdown, or leave the interior colour as default if the cell remains empty. I have more than 3 criteria and hence need some VBA code... Im guessing a ThisWorkbook Change event? but im only guessing!! can someone help me out? regards steve |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Format with VBA - Interior Colour of cell based onvalue from in-cell dropdown
Use a delimited list for the data validation values (type the values in
the Source text box, instead of referring to a range on the worksheet), and selecting a value will trigger the Worksheet_Change event. Steve wrote: yes (unfortunately) i think 2000 and above is different - but in 97 the worksheet change event does not execute after a selection from a incell dropdown list?? trying to think of a work around but my imagination is failing me.... Frank Kabel wrote in message ... Hi are you using Excel 97? -- Regards Frank Kabel Frankfurt, Germany Steve wrote: thanks frank!! however for some reason making a selection from the incell dropdown list does not fire the worksheet change event? I need to double click inside the actual cell and then click outside again to make excel realise the cell's value has changed... any ideas? Frank Kabel wrote in message ... Hi below a repost ------ Hi conditional format only accepts 3 conditions though you have a fourth if you include the default format. If you only want to apply different FONT colors based on NUMBERS, you can define up to 6 different styles. See: http://www.mcgimpsey.com/excel/conditional6.html for instructions how to do it For everything else you'll need VBA code (e.g. process the worksheet_change event and apply your format based on the cell values). The following will color the entry in cell A1:A100 based on its value: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("A1:A100")) Is Nothing Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False With Target Select Case .Value Case "Red": .Interior.ColorIndex = 3 Case "Blue": .Interior.ColorIndex = 10 'etc. End Select End With CleanUp: Application.EnableEvents = True End Sub -- Regards Frank Kabel Frankfurt, Germany Steve wrote: hi all, new to VBA - any help greatly appreciated I have searched high and low, possibly not asking the right questions however each cell in my range (D3:AH17) has a Data Validation drop down box. The dropdown is the same in every cell and contains seven values, SL, RL, RDO, TD, PH, SPL, SL (refering to types of leave etc) I need to conditional format the cell (change the interior colour) depending on what the user selects from the dropdown, or leave the interior colour as default if the cell remains empty. I have more than 3 criteria and hence need some VBA code... Im guessing a ThisWorkbook Change event? but im only guessing!! can someone help me out? regards steve -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional Format based on existing cell colour ? | Excel Discussion (Misc queries) | |||
| conditional colour fill a row - based on cell value =100% | Excel Worksheet Functions | |||
| conditional cell format based on cell in same row, previous column | Excel Worksheet Functions | |||
| Conditional Format - Formula to Colour Every 3rd Cell in Offset Range | Excel Discussion (Misc queries) | |||
| Conditional format if cell=0 then font colour same as background . | Excel Discussion (Misc queries) | |||
![Steve[_52_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
