Excel: drop down list colouring based on "dependent" list

[vikosh]

I am looking for a solution for my excel sheet and am not finding any on the internet.
I have the following data for the dropdown list. I'd like my dropdown list to reflect Column B and the color from Column D according to Column C value that corresponds to the cell from Column B.
I seem to not manage to do the conditional formatting because the values in Column B cells sometimes are the same.
I'd like to have a dropdown list based on the values from column B - done through data validation.
Then, i'd like those cells get the colour mentioned in the column D based on the dependent/additional data from column C.
For certain reasons i cannot combine columns, i.e. have "sickness approved" together in one cell and then colour code it according to that (i wish... then it would be so easy *cry)
So, if i choose "course" value from Column B that corresponds to "approved" from Column C, then that cell has to change color to "green", if i choose "100%" or "90%" from column B in the drop down menu, then that cell changes color to "white", if i choose "leave" that corresponds to "pending" then colour "yellow", etc. For the last value - block - when i choose that empty value from the list, then the cell changes to grey color with stripy pattern.

Column A	Column B	Column C	Column D
Present	100%		white
Present	90%		white
Absent	sickness	approved	green
Absent	sickness	not approved	red
Absent	sickness	pending	yellow
Absent	course	approved	green
Absent	course	not approved	red
Absent	course	pending	yellow
Block			grey colour with stripy pattern

I hope my explanation is not too confusing.
Are the ways to achieve this? Whether with the inbuilt Excel features or Macro code?
I tried to look around Data Validation and Conditional Formatting, but haven't found options that would help me solve this.
Thank you in advance,
Vik

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

So, if i choose "course" value from Column B that corresponds to "approved" from Column C,

If your drop-down contains the values from column B and the user chooses "course" how would we know whether the particular "course" chosen in the drop-down corresponds to "approved", "not approved" or "pending"?

 

[vikosh]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)


If your drop-down contains the values from column B and the user chooses "course" how would we know whether the particular "course" chosen in the drop-down corresponds to "approved", "not approved" or "pending"?

Thank you. I will do that.

The user wants to just have three versions of the same. They apparently know which one to choose: first, second or the third.

 

[Peter_SSs]

We still do not know your Excel version so do not know what functions and resources are available to you. Please review the first paragraph in my previous post.
Also, will people using this workbook be using the same version? If not, please advise what version(s) they will be using.

They apparently know which one to choose: first, second or the third

They might know but Excel needs a way to know. For example, now that a value has been chosen from the drop-down in cell F2, how can Excel determine which one was chosen, since three are identical?

vikosh.xlsm
	A	B	C	D	E	F
1	Column A	Column B	Column C	Column D
2	Present	100%		white		course
3	Present	90%		white
4	Absent	sickness	approved	green
5	Absent	sickness	not approved	red
6	Absent	sickness	pending	yellow
7	Absent	course	approved	green
8	Absent	course	not approved	red
9	Absent	course	pending	yellow
10	Block			grey colour with stripy pattern
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
F2	List	=$B$2:$B$10

For certain reasons i cannot combine columns, i.e. have "sickness approved" together in one cell

I am assuming that you mean remaining showing in the worksheet. If so, see if this might work for you.

• In columns A:D I have the sample values you provided.
• In column E I have a formula (see mini sheet below) copied down. (These A:E values could be anywhere on any worksheet and column E can be hidden if you want)
• Data Validation is set up to use E2:E10 values so the user sees this when they click the drop-down
  
  
  
  
  
  
• Use the following Worksheet_Change code, This code applies the relevant cell formatting from the combined value chosen and then replaces the cell value with just the left part of the combined value. To implement ..
  1. Right click the sheet name tab and choose "View Code".
  2. Copy and Paste the code below into the main right hand pane that opens at step 1. Edit the target DV range as noted in the code.
  3. Close the Visual Basic window & test.
  4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).
  
  

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  Dim Bits As Variant
  Dim clr As Long
  
  Set Changed = Intersect(Target, Range("G2:G15"))  '<- Change the range to your Data validation cell(s) range
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      Bits = Split(c.Value & "|", "|")
      Select Case LCase(Bits(1))
        Case "approved": clr = RGB(146, 208, 80)    'green
        Case "not approved": clr = RGB(255, 0, 0)   'red
        Case "pending": clr = RGB(255, 255, 0)      'yellow
        Case Else
          If Bits(0) = "" Then
            clr = RGB(191, 191, 191)                'grey
            c.Interior.Pattern = xlLightHorizontal  'stripes
          Else
            clr = RGB(255, 255, 255)                'white
          End If
      End Select
      c.Interior.Color = clr
      c.Value = Bits(0)
    Next c
    Application.EnableEvents = True
  End If
End Sub

Here is my sample worksheet after I have chosen values from the DV drop-downs in G2:G15. The stripes do not show up with XL2BB but I have included an image below as well.

vikosh.xlsm
	A	B	C	D	E	F	G
1	Column A	Column B	Column C	Column D
2	Present	100%		white	100%
3	Present	90%		white	90%		sickness
4	Absent	sickness	approved	green	sickness|approved
5	Absent	sickness	not approved	red	sickness|not approved		90%
6	Absent	sickness	pending	yellow	sickness|pending		course
7	Absent	course	approved	green	course|approved		100%
8	Absent	course	not approved	red	course|not approved		sickness
9	Absent	course	pending	yellow	course|pending		sickness
10	Block			grey colour with stripy pattern
11							course
12							course
13
14
15
Sheet3

Cell Formulas
Range	Formula
E2:E10	E2	=IF(B2="","",TEXT(B2,"0%"))&IF(C2="","","|"&C2)

Cells with Data Validation
Cell	Allow	Criteria
G2:G15	List	=$E$2:$E$10

 

[vikosh]

We still do not know your Excel version so do not know what functions and resources are available to you. Please review the first paragraph in my previous post.
Also, will people using this workbook be using the same version? If not, please advise what version(s) they will be using.


They might know but Excel needs a way to know. For example, now that a value has been chosen from the drop-down in cell F2, how can Excel determine which one was chosen, since three are identical?

vikosh.xlsm
	A	B	C	D	E	F
1	Column A	Column B	Column C	Column D
2	Present	100%		white		course
3	Present	90%		white
4	Absent	sickness	approved	green
5	Absent	sickness	not approved	red
6	Absent	sickness	pending	yellow
7	Absent	course	approved	green
8	Absent	course	not approved	red
9	Absent	course	pending	yellow
10	Block			grey colour with stripy pattern
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
F2	List	=$B$2:$B$10

I am assuming that you mean remaining showing in the worksheet. If so, see if this might work for you.

• In columns A:D I have the sample values you provided.
• In column E I have a formula (see mini sheet below) copied down. (These A:E values could be anywhere on any worksheet and column E can be hidden if you want)
• Data Validation is set up to use E2:E10 values so the user sees this when they click the drop-down
  
  View attachment 118929
  
  
• Use the following Worksheet_Change code, This code applies the relevant cell formatting from the combined value chosen and then replaces the cell value with just the left part of the combined value. To implement ..
  1. Right click the sheet name tab and choose "View Code".
  2. Copy and Paste the code below into the main right hand pane that opens at step 1. Edit the target DV range as noted in the code.
  3. Close the Visual Basic window & test.
  4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).
  

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  Dim Bits As Variant
  Dim clr As Long
 
  Set Changed = Intersect(Target, Range("G2:G15"))  '<- Change the range to your Data validation cell(s) range
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      Bits = Split(c.Value & "|", "|")
      Select Case LCase(Bits(1))
        Case "approved": clr = RGB(146, 208, 80)    'green
        Case "not approved": clr = RGB(255, 0, 0)   'red
        Case "pending": clr = RGB(255, 255, 0)      'yellow
        Case Else
          If Bits(0) = "" Then
            clr = RGB(191, 191, 191)                'grey
            c.Interior.Pattern = xlLightHorizontal  'stripes
          Else
            clr = RGB(255, 255, 255)                'white
          End If
      End Select
      c.Interior.Color = clr
      c.Value = Bits(0)
    Next c
    Application.EnableEvents = True
  End If
End Sub

Here is my sample worksheet after I have chosen values from the DV drop-downs in G2:G15. The stripes do not show up with XL2BB but I have included an image below as well.

vikosh.xlsm
	A	B	C	D	E	F	G
1	Column A	Column B	Column C	Column D
2	Present	100%		white	100%
3	Present	90%		white	90%		sickness
4	Absent	sickness	approved	green	sickness|approved
5	Absent	sickness	not approved	red	sickness|not approved		90%
6	Absent	sickness	pending	yellow	sickness|pending		course
7	Absent	course	approved	green	course|approved		100%
8	Absent	course	not approved	red	course|not approved		sickness
9	Absent	course	pending	yellow	course|pending		sickness
10	Block			grey colour with stripy pattern
11							course
12							course
13
14
15
Sheet3

Cell Formulas
Range	Formula
E2:E10	E2	=IF(B2="","",TEXT(B2,"0%"))&IF(C2="","","|"&C2)

Cells with Data Validation
Cell	Allow	Criteria
G2:G15	List	=$E$2:$E$10

View attachment 118930

Thank you so much. I think that should work. I'll test it and let you know if something didn't work.

 

[Peter_SSs]

OK, cheers. Thanks for the follow-up.