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

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

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

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

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

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