You are using an out of date browser. It may not display this or other websites correctly.

You should upgrade or use an alternative browser.

You should upgrade or use an alternative browser.

- Thread starter Calypso88d
- Start date

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

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

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

Last edited:

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.

Is there a way to increase the size of characters allowed in conditional formatting formula bar? My formula is too long.

I have 89 labels in the dropdown list and more than 20 columns eg Grapes, Oranges, Bananashow long is the formula

maybe a better way to do the formula if its so different to what i posted

Is there any alternative to this? I tried using google but I'm not succeeding

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 ........

I was also thinking the vba route

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 ........

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?

Those are are the only two rules, yes## Calypso88d

This thread can move toVBA 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?

i'm assuming in the dropdown 89 - more than just nice and very niceI 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

as mentioned before

maybe post the formula as wellmeanwhile can you send a copy of the spreadsheet with the 20columns and 80 dropdowns in case there a pattern

Sorry, my mistake those are not the rules. I thought you were referring to the explanation written on the spreadsheet I had attached previously.... 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.

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.

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.## 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.

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

Which dropdown value column value combination should give which color?

"Nice" value from dropdown list should always give me yellow colour under Oranges column.## Calypso88d

Do You know those rules - how that file should work?

Which dropdown value column value combination should give which color?

"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 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

Hi Etaf,

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

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

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

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