• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Fill colour based on dropdown list selection

C

Calypso88d

New Member
Hi everyone,

Attached is my spreadsheet. I want to fill colour in different columns based on what I select in the dropdown list in excel. Please help.

Your help will be very much appreciated.

Thank you.
 

Attachments

  • Spreadsheet 1.xlsx
    9.2 KB · Views: 2
i dont see what the rules are for how you colour the cells
conditional formatting may help here

But why the different columns coloured ????

I can see that different colours could easily be added based on the dropdown value - using conditional formatting


Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.
 
is this homework of somekind
if so - you really need to work out how to do it

What version of excel are you using - would be worth updating your profile to show version as solutions will be dependant on what version you have and therefore what functions available.
Excel Versions are like: 2003/2007/2010/2013/2016/2019/2021/365 , also rather than show more than 1 version if you have them , which version you will be using the solution for. As an awful lot of new functions have now been added to the newer versions


anyway
=OR(AND($A2="Nice",B$1="oranges"),AND($A2="very Nice",OR(B$1="Grapes",B$1="bananas")))

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
$B$2:$E$6 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=OR(AND($A2="Nice",B$1="oranges"),AND($A2="very Nice",OR(B$1="Grapes",B$1="bananas")))

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK
 

Attachments

  • Spreadsheet 1 -ETAF.xlsx
    10 KB · Views: 4
Last edited:
Hi Etaf,

It's not homework, I'm still an excel newbie.

I really appreciate your help and prompt reply. I would like to be an expert like you one day.

Thank you.
 
Hi everyone,

Is there a way to increase the size of characters allowed in conditional formatting formula bar? My formula is too long.
 
how long is the formula
maybe a better way to do the formula if its so different to what i posted

i think its still 255 characters
 
there maybe some factor that could be used - to group some of the vlaues
otherwise - maybe VBA will be needed

I dont provide VBA solutions

It maybe worth reporting the post - see "report" at bottom of post and asking an admin if this could be closed and thenreopen a new thread - and in the title perhaps making sure you what a VBA solution

meanwhile can you send a copy of the spreadsheet with the 20columns and 80 dropdowns in case there a pattern

as if you only have 20 columns - BUT 80- dropdowns, then the same cell will be coloured based on more than 1 dropdown

so what cell is coloured - Grapes , based on what dropdowns - just one or a few

anyway ........
 
ETAF said:
there maybe some factor that could be used - to group some of the vlaues
otherwise - maybe VBA will be needed

I dont provide VBA solutions

It maybe worth reporting the post - see "report" at bottom of post and asking an admin if this could be closed and thenreopen a new thread - and in the title perhaps making sure you what a VBA solution

meanwhile can you send a copy of the spreadsheet with the 20columns and 80 dropdowns in case there a pattern

as if you only have 20 columns - BUT 80- dropdowns, then the same cell will be coloured based on more than 1 dropdown

so what cell is coloured - Grapes , based on what dropdowns - just one or a few

anyway ........
Click to expand...
I was also thinking the vba route
 

Calypso88d​

This thread can move to VBA Macros ( ~no need to open a new thread ).
Your I have 89 labels in the dropdown list and more than 20 columns eg Grapes, Oranges, Bananas
and Your file shows ... two rules for those colors.
Are those all rules?
if Yes then okay
otherwise ...
... how do You manage those missing rules?
 
vletm said:

Calypso88d​

This thread can move to VBA Macros ( ~no need to open a new thread ).
Your I have 89 labels in the dropdown list and more than 20 columns eg Grapes, Oranges, Bananas
and Your file shows ... two rules for those colors.
Are those all rules?
if Yes then okay
otherwise ...
... how do You manage those missing rules?
Click to expand...
Those are are the only two rules, yes
 
what two rules , sorry
I would like the colour yellow to fill Oranges when "Nice" is slected, the colour yellow should fill in again under grapes and Bananas only when "Very Nice" is selected
Click to expand...
i'm assuming in the dropdown 89 - more than just nice and very nice
as mentioned before

meanwhile can you send a copy of the spreadsheet with the 20columns and 80 dropdowns in case there a pattern
Click to expand...
maybe post the formula as well
 
... the only two rules, yes
Means that only those three labels and only those four columns can have colors - others ... no!
You haven't use third label ... that means - no color.
As well as Apples won't have color.
 
vletm said:
... the only two rules, yes
Means that only those three labels and only those four columns can have colors - others ... no!
You haven't use third label ... that means - no color.
As well as Apples won't have color.
Click to expand...
Sorry, my mistake those are not the rules. I thought you were referring to the explanation written on the spreadsheet I had attached previously.
 

Calypso88d​

I referred only and only Your given file.
otherwise ...
... how do You manage those missing rules?
It'll be a challenge to offer any kind of solution if something basic details are missing.
 
vletm said:

Calypso88d​

I referred only and only Your given file.
otherwise ...
... how do You manage those missing rules?
It'll be a challenge to offer any kind of solution if something basic details are missing.
Click to expand...
The solution Etaf provided worked it's just that my formula is too long and doesn't fit into the conditional formatting formula bar hence the reason why we both agreed on vba.

It's just unfortunate I don't know vba, lol. I'm doomed....
 
vletm said:

Calypso88d​

Do You know those rules - how that file should work?
Which dropdown value column value combination should give which color?
Click to expand...
"Nice" value from dropdown list should always give me yellow colour under Oranges column.
"Very nice" value from dropdown list should always give me a yellow colour under Grapes and Bananas column.
Another value from the dropdown list should give me a yellow colour under e.g Apples and Bananas column

I only provided those two values from the dropdown list as an example of how the colour coding should work. Each value in the dropdown list has it's own rule as to which colums should be shaded with a yellow colour, some values in the dropdown list share the same rule
 
As two times tried to get an answer:
how do You manage those missing rules?
Without an answer ... it's really a challenge to offer more than those ... Your given rules.
 
you have 89 dropdowns - but only 20 columns
as asked a few times now
that means you can have unique columns for 20 of the dropdowns - so what are all the combinations

again as asked - it maybe possible to group things together - just a thought

BUT thats upto you if you want to reply to my posts post No 11 & post No 15

otherwise , i will leave for a VBA solution once moved - or closed and new thread started depending on MOD
 
ETAF said:
you have 89 dropdowns - but only 20 columns
as asked a few times now
that means you can have unique columns for 20 of the dropdowns - so what are all the combinations

again as asked - it maybe possible to group things together - just a thought

BUT thats upto you if you want to reply to my posts post No 11 & post No 15

otherwise , i will leave for a VBA solution once moved - or closed and new thread started depending on MOD
Click to expand...
Hi Etaf,

Attached is the sample data, sorry I wasn't ignoring your posts.
 

Attachments

  • Sample_Data.xlsx
    10.8 KB · Views: 3
so i have made up a reference grid
on sheet3
which has a 1 in the row/column where you want yellow

not on sheet1 I have used conditional formatting , using this formula
=SUMPRODUCT((Sheet3!$B$2:$U$11)*(Sheet3!$A$2:$A$11=Sheet1!$A2)*(Sheet3!$B$1:$U$1=Sheet1!B$1))

NOW when you change the drop down - which i suspect is what you want to do - it will highlight the fruits/columns based on that gris

but i dont think your exmple posted represents the REAL data

any way
on sheet 1 change any dropdown on A column and the fill will change to match the patten on sheet3
 

Attachments

  • Sample_Data - ETAF.xlsx
    15 KB · Views: 1
You must log in or register to reply here.
Back
Top