How to change color

November 18, 2020 at 02:12:43
Specs: Windows 10
Hi . I have 4 column N to Q with three attributes in drop down-Yes/No/Maybe. I need column R column to automatically change color. Say if N=Yes ,O=Maybe, P=No,Q=Maybe , R hould be Blue color,. Similarly i need different combination. How to do this? since conditional formatting not possible for 64 combination

See More: How to change color


#1
November 18, 2020 at 08:55:10
I don't have an answer for you, I believe you are going to need a VBA solution,
and my VBA skills are just above nil. Sorry.

But I do have one observation,
by my calculation if you intend to give each combination a different color
and with 81 different combinations and using the color wheel of 12 colors
you are still going to need at least 6 shades of each color.

It will be very colorful, but it may be a bit difficult to differentiate between the
different shades.

With a bit more information on what it is your trying to accomplish,
we may be able to offer a different strategy to get you what you want.


MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

#2
November 18, 2020 at 09:30:12
Whenever possible - KISS; keep anything you want to view and distinguish one from another - keep it simple... Too much colour is just as konphusing as none... Consider even type faces too (italics, bold etc.)

Reply ↓  Report •

#3
November 18, 2020 at 23:39:19
Super-duper hyper-good advice from both of those guys!

-- Jeff, in Minneapolis


Reply ↓  Report •

Related Solutions

#4
November 19, 2020 at 01:27:24
Do give some authentic and genuine answer .I think this platform is not for joke.

Reply ↓  Report •

#5
November 19, 2020 at 02:02:58
Our comments are advisory.. and Mike #1 has asked for more details from you.

Reply ↓  Report •

#6
November 19, 2020 at 02:11:06
A B C D E
No No No Maybe
Maybe Maybe Maybe Maybe


Col A,B,C D have drop down Yes,No,Maybe value . I need Col E to change diff color based on combination of attribute of A,B,C,D.


Reply ↓  Report •

#7
November 19, 2020 at 02:12:47
Also, If i Manually add all 64 condition for one , how can i copy paste in other cell since say if Col E3 to copy paste in Col E4 , i need to change all col a,b,c,d, manually for 64 combination. Any shortcut for that .

Reply ↓  Report •

#8
November 19, 2020 at 06:22:28
If you are using Excel 2003 or earlier, then the following will not work.

If your data looks like this:

    N       O       P        Q       R
1) yes     no      no      maybe
2) yes     no     maybe     yes	

This is the basic format you will need to use:

To Highlight Cell R2

1) Select your cell or range of cells: IE R1:R10
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.
5) Enter the formula:

=AND(N1="yes",O1="no",P1="maybe",Q1="yes")

6) Click on the Format button
7) Select the Fill Tab
8) Select a color
9) Click OK
10) Click OK

To Highlight Cell R1

1) Select your cell or range of cells: IE R1:R10
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.
5) Enter the formula:

=AND(N1="yes",O1="no",P1="no",Q1="maybe")

6) Click on the Format button
7) Select the Fill Tab
8) Select a color
9) Click OK
10) Click OK


Continue this process, changing the formula each time,
until you have accounted for all 81 possible combinations.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#9
November 19, 2020 at 09:42:06
Hey Mike,

You're the CF guru, so check this out...

I found some code that will easily create all 81 combinations of the Yes, No, Maybe choices across 4 columns.

Each of those combinations could be concatenated into a single, unique value, (e.g. YesMaybeNoYes, NoNoNoNo, etc.).

Is there a way to write a CF formula that would do the Concatenation, even if it just resulted in a single fill color for all cells?

If I had that formula, I think I could write some VBA code that could loop through the 81 combinations, setting a different rule and CF Fill color for each combination.

Not sure, but I would need that Concatenation CF formula in order to try anything. Basically what I am trying to avoid is having to hard code the 81 different combinations into the code.

message edited by DerbyDad03


Reply ↓  Report •

#10
November 19, 2020 at 10:33:50
Not completely sure what your asking for,

but perhaps build a table of 81 concatenated cells
with the concatenated cells in AA and the corresponding color in AB
and do a VLOOKUP()

Something like that??

Selecting the corresponding color is the hangup with doing the CF manually
that's why I suggested a VBA solution..
I believe the OP wants a unique color for all 81 combinations

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#11
November 19, 2020 at 12:31:31
Ankita89,

If it wasn't clear, I was saying that I strongly agree with what
mmcconaghy and trvlr said. It sounds so much like what I
would have said that I wanted to comment on it.

When mmcconaghy asked for more information on what
you are trying to accomplish, he was asking you why you want
the different colors. Because, as he implied, it will be impossible
to distinguish between that many different colors by eye. Your
goal may be to reduce confusion and make reading the table
easier, but the multitude of colors will do the opposite.

You keep referring to 64 colors. Is that because some of the
81 combinations of yes, no, and maybe are not possible?

-- Jeff, in Minneapolis


Reply ↓  Report •

#12
November 19, 2020 at 12:41:28
Never mind, for the time being.

I'm working on some code that sets a CF rule for all 81 different combinations but I'm struggling with getting it to use a different color for each combination.

The code seems to be able to create the 81 AND formulas based on the contents of each N:Q set, but I can't get it to use a different color each time. (It applies the same color to every combination)

However, if I manually edit the fill color through the CF wizard for any given combination after the code has initially set the color, it applies that new color to any N:Q set that contains that combination. (determined by editing the contents of a few cells)

So it appears that the 81 rules are being created correctly, but the fill color is not be set differently for each rule.

Need to keep playing.

(None of this is to say that I think 81 different colors in a table is the correct way to go. For me, it's merely an exercise in coding at this point.)

message edited by DerbyDad03


Reply ↓  Report •

#13
November 19, 2020 at 14:46:43
The following code will create 81 Conditional Formatting rules based on the 81 different 4-value combinations of Yes, No, Maybe.

Just to prove that the concept works, I chose the Fill "colors" for this version to consist of the 56 colors in the default Excel color pallet, plus 25 of the same 56 colors plus a Fill pattern. As a example...

Yes, Yes, No, Maybe will be formatted solid Yellow
Maybe, Yes, Maybe, Yes will be formatted Yellow with a black grid.

With a little more work, it might be possible to come up with 81 distinct colors. I'm not sure at this point.

In order for the code to work, the 81 different combinations will need to be placed in N1:Q81. The code will read the values in each set of 4 cells (N:Q) and create a CF rule for that combination. As written, the code will apply the conditional formatting to R1:R81. That range is easily changed if you don't need that many cells Conditionally Formatted. I applied the CF to the entire range just so that I could see every color and color with pattern.

If you need the code that creates the 81 different combinations, I can supply that also. I didn't write that code, but I'll share it and give credit where credit is due. Just let me know.

Here is the code creates the Conditional Formatting rules. Let me know what you think.

Sub ColorMyWorld()
'
'81 CF rules, all with different Fill colors of Fill color with grid
'
 
'Clear current CF in Range

 Range("R1:R81").FormatConditions.Delete
     Range("R1:R81").Select
     
'Loop through Combinations
     For rw = 1 To 81
    Application.CutCopyMode = False
    
'Create Conditional Formatting Rules
''Rules are based on values in N:Q, current Row of loop
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND(N1=" & """" & Cells(rw, "N") & """" & _
        ",O1=" & """" & Cells(rw, "O") & """" & _
        ",P1=" & """" & Cells(rw, "P") & """" & _
        ",Q1=" & """" & Cells(rw, "Q") & """" & ")"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    
''Set Colors/Pattern for CF
    With Selection.FormatConditions(1).Interior
        If rw <= 56 Then
        .ColorIndex = rw
        Else:
        .ColorIndex = rw - 55
        .Pattern = 15
        End If
    End With
        Selection.FormatConditions(1).StopIfTrue = False
    Next
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#14
November 19, 2020 at 18:02:30
DerbyDad
You are a marvel. :-)

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#15
November 19, 2020 at 20:35:18
Thanks. That was kind of fun.

Probably useless, but fun nonetheless. ;-)


message edited by DerbyDad03


Reply ↓  Report •

#16
November 19, 2020 at 21:23:17
Doesn't work for me .

Reply ↓  Report •

#17
November 20, 2020 at 04:52:13
Telling me that it "doesn't work" doesn't give me enough information to be able to help you.

We can't see your worksheet from where we are sitting, so, as Mike said earlier, you have to provide details or there is nothing we can do to help.

What does "doesn't work" mean?

Does it present an error? What is that error?
Does it not do anything?
Does it do something? What does it do?

Are you familar enough with VBA to be able to use the debugging tools?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#18
November 20, 2020 at 06:19:07
Just to show that the code is working in my workbook, I've attached a few screenshots.

If you need me to walk you through the process, I can do that. Please ask nicely.

This is the raw data that I used to created the combinations

https://i.imgur.com/3HJHUTy.jpg

This is the sheet after the combinations have been created, but no Conditional Formatted applied yet. This was done using a macro, which I can provide if you need it.

The list of all 81 combinations needs to be created in order for the code that I posted in Response #13 to work.

https://i.imgur.com/3CXTgN8.jpg

This is the sheet after running the macro posted in Response #13. The fill colors in Column R are the result of the Conditional Formatting rules that the code built.

https://i.imgur.com/vPKR3qb.jpg

This is a test of the Conditional Formatting. Q6 was changed, by selecting a drop down choice, to match Q5. Since N6:Q6 now matches N5:Q5, the CF fill color in R6 matches the CF fill color in R5.

https://i.imgur.com/HtLKc0f.jpg

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#19
November 20, 2020 at 16:00:52
DD03,

Oddly, the screenshots don't show up on the imgr.com pages.
The usual dark gray background color and the headers display
at the top of the page, but then they disappear again and nothing
more happens. Problem at imgur ?

-- Jeff, in Minneapolis


Reply ↓  Report •

#20
November 20, 2020 at 18:09:05
Jeff

The imgr.com pages worked fine for me.
Do you have any spam blockers on?

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

#21
November 20, 2020 at 22:41:09
No spam blocker. I can view an image on imgur.com
that was linked from reddit.com. This is using Edge --
the version that came with my computer four years ago.
I can view the screenshots in Firefox.

-- Jeff, in Minneapolis


Reply ↓  Report •

#22
November 21, 2020 at 02:35:35
Ah the continuing saga of win-10, Edge browser continues...

Reply ↓  Report •

#23
November 24, 2020 at 03:18:26
Dear Derbydad03

I am a layman in coding but after applying your code to VBA i am not getting what i want. let me explain u what i need . I have row with CF from N3:Q3 to N133:Q133. All these 4 column have 3 attribute with color :
eg: N3 cell have Yes :Green color, No: Red, Maybe: yellow same for all N3:Q133. No i want R column from R3 to fill automatically for 81 different combination you have made in screenshot . Else if no selection from N3:Q3, no color fill . Also i don't need any color grid.

Hope thi is clear now .


Reply ↓  Report •

#24
November 24, 2020 at 08:28:22
You want cells N
to be colored Green if it contains the TEXT string Yes
to be colored Red if it contains the TEXT string No
to be colored Yellow if it contains the TEXT string Maybe

Then you want cells R to be colored also,
using DerbyDad's color scheme for Yes/No/Maybe combinations.

Is this correct?

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

#25
November 24, 2020 at 08:35:25
For Column N try this:

For Answer Yes

1) Select your cell or range of cells: IE N1:N80
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.
5) Enter the formula:

=N1="Yes"

6) Click on the Format button
7) Select the Fill Tab
8) Select a Green color
9) Click OK
10) Click OK

For Answer No

1) Select your cell or range of cells: IE N1:N80
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.
5) Enter the formula:

=N1="No"

6) Click on the Format button
7) Select the Fill Tab
8) Select a Red color
9) Click OK
10) Click OK

For Answer Maybe

1) Select your cell or range of cells: IE N1:N80
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.
5) Enter the formula:

=N1="Maybe"

6) Click on the Format button
7) Select the Fill Tab
8) Select a Yellow color
9) Click OK
10) Click OK

Use DerbyDad's VBA for the rest.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#26
November 24, 2020 at 12:34:59
re:

"No i want R column from R3 to fill automatically for 81 different combination you have made in screenshot."

Filled with what? If you are looking for 81 different fill "colors", 1 for each of the 81 combinations, then you need 81 different CF rules. That's what my VBA creates.

re: "Also i don't need any color grid"

The reason for the "color grid" is that there are only 56 colors in the basic Excel color palette. To get 81 different "colors" I simply repeated some of the same colors and added a grid pattern.

For example, look at the following screen shot.

Yes, Maybe, Maybe, No uses the same pinkish fill color as Maybe, Maybe, Maybe, Maybe except that Maybe, Maybe, Maybe, Maybe includes a grid to distinguish it from Yes, Maybe, Maybe, No.

The same process holds for No, Yes, Yes, Yes and Maybe, Yes, Maybe, Maybe where both use the same aquamarine fill, but one adds the grid.

https://i.imgur.com/qrwFsUm.jpg

If you absolutely require 81 different fill colors with no pattern, that is something that I would have to work on since I'm not sure how to code that yet. There are only 56 colors in the basic Excel palette, so that's why I added patterns to some of the fill colors. I'll play around and see what I can come up with.

As far as applying the CF rules to R3:R133, all you need to do is create the 81 rules using the code that I provided in an separate sheet, then Copy them to the cells that you really want them applied to, using Copy...Paste Special - Formats.

I have a suspicion that you are trying to run my code against your current data set in N3:Q133. That's not what should be done. The code should be run against a data set in N1:Q81 that contains each unique combination of your 3 values. That will create the 81 rules that can then be copied to R3:R133. After running my code, what you would do is::

Select R1
Right Click - Copy
Select R3:R133 in the proper sheet
Right Click - Paste Special
Click the radial button for Formats
Click OK.

If you need help setting up the 81 unique combinations so that you can run my code against that list, let me know.

Here is a version of the code that eliminates White as a fill color. That way the cell remains white if none of the 81 combinations is present in that row.

Sub ColorMyWorld_V2()
'
'81 CF rules, all with different Fill colors or Fill color with grid
'
 
'Clear current CF in Range

 Range("R1:R81").FormatConditions.Delete
     Range("R1:R81").Select
     
'Loop through Combinations
     For rw = 1 To 81
    
'Create Conditional Formatting Rules
''Rules are based on values in N:Q, current Row
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND(N1=" & """" & Cells(rw, "N") & """" & _
        ",O1=" & """" & Cells(rw, "O") & """" & _
        ",P1=" & """" & Cells(rw, "P") & """" & _
        ",Q1=" & """" & Cells(rw, "Q") & """" & ")"
    
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    
''Set Colors/Pattern for CF, Don't use Black or White
''For Rows > 54, repeat ColorIndexes 3 - 29, fill with grid
    With Selection.FormatConditions(1).Interior
        If rw < 55 Then
        .ColorIndex = rw + 2
        Else:
        .ColorIndex = rw - 52
        .Pattern = 15
        End If
    End With
    
        Selection.FormatConditions(1).StopIfTrue = False
    Next
End Sub



How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#27
November 24, 2020 at 17:26:36
With some help from a friend, I was able to create some code that will display 135 different colors in
Column A and the VBA name for those colors in Column B.

Run the code in a new workbook, pick the 81 colors that you want to use for your CF rules, and list them,
by name, back here in the forum. I can then modify the code I posted earlier and eliminate the
"patterns" (grid) used for the duplicate colors.

I wish you luck. As was hinted at way back in Responses 2 & 3, I think that you will have a hard time
finding 81 colors that are different enough that you'll be able to make a clear distinction between
them all.

Here you go...

Sub rgbLook()
    prettyColors = Array(rgbAliceBlue, rgbAntiqueWhite, rgbAqua, rgbAquamarine, rgbAzure, rgbBeige, rgbBisque, rgbBlack, rgbBlanchedAlmond, rgbBlue, _
        rgbBlueViolet, rgbBrown, rgbBurlyWood, rgbCadetBlue, rgbChartreuse, rgbCoral, rgbCornflowerBlue, rgbCornsilk, rgbCrimson, rgbDarkBlue, _
        rgbDarkCyan, rgbDarkGoldenrod, rgbDarkGreen, rgbDarkGray, rgbDarkKhaki, rgbDarkMagenta, rgbDarkOliveGreen, rgbDarkOrange, rgbDarkOrchid, _
        rgbDarkRed, rgbDarkSalmon, rgbDarkSeaGreen, rgbDarkSlateBlue, rgbDarkSlateGray, rgbDarkTurquoise, rgbDarkViolet, rgbDeepPink, rgbDeepSkyBlue, _
        rgbDimGray, rgbDodgerBlue, rgbFireBrick, rgbFloralWhite, rgbForestGreen, rgbFuchsia, rgbGainsboro, rgbGhostWhite, rgbGold, _
        rgbGoldenrod, rgbGray, rgbGreen, rgbGreenYellow, rgbHoneydew, rgbHotPink, rgbIndianRed, rgbIndigo, rgbIvory, _
        rgbKhaki, rgbLavender, rgbLavenderBlush, rgbLawnGreen, rgbLemonChiffon, rgbLightBlue, rgbLightCoral, rgbLightGoldenrodYellow, rgbLightGray, _
        rgbLightGreen, rgbLightPink, rgbLightSalmon, rgbLightSeaGreen, rgbLightSkyBlue, rgbLightSlateGray, rgbLightSteelBlue, rgbLightYellow, _
        rgbLime, rgbLimeGreen, rgbLinen, rgbMaroon, rgbMediumAquamarine, rgbMediumBlue, rgbMediumOrchid, rgbMediumPurple, rgbMediumSeaGreen, rgbMediumSlateBlue, _
        rgbMediumSpringGreen, rgbMediumTurquoise, rgbMediumVioletRed, rgbMidnightBlue, rgbMintCream, rgbMistyRose, rgbMoccasin, rgbNavajoWhite, rgbNavyBlue, _
        rgbOldLace, rgbOlive, rgbOliveDrab, rgbOrange, rgbOrangeRed, rgbOrchid, rgbPaleGoldenrod, rgbPaleGreen, rgbPaleTurquoise, rgbPaleVioletRed, _
        rgbPapayaWhip, rgbPeachPuff, rgbPeru, rgbPink, rgbPlum, rgbPowderBlue, rgbPurple, rgbRed, rgbRosyBrown, rgbRoyalBlue, _
        rgbSalmon, rgbSandyBrown, rgbSeaGreen, rgbSeashell, rgbSienna, rgbSilver, rgbSkyBlue, rgbSlateBlue, rgbSlateGray, rgbSnow, _
        rgbSpringGreen, rgbSteelBlue, rgbTan, rgbTeal, rgbThistle, rgbTomato, rgbTurquoise, rgbYellow, rgbYellowGreen, rgbViolet, _
        rgbWheat, rgbWhite, rgbWhiteSmoke)
        
    prettyNames = Split("rgbAliceBlue, rgbAntiqueWhite, rgbAqua, rgbAquamarine, rgbAzure, rgbBeige, rgbBisque, rgbBlack, rgbBlanchedAlmond, rgbBlue, " & _
        "rgbBlueViolet, rgbBrown, rgbBurlyWood, rgbCadetBlue, rgbChartreuse, rgbCoral, rgbCornflowerBlue, rgbCornsilk, rgbCrimson, rgbDarkBlue, " & _
        "rgbDarkCyan, rgbDarkGoldenrod, rgbDarkGreen, rgbDarkGray, rgbDarkKhaki, rgbDarkMagenta, rgbDarkOliveGreen, rgbDarkOrange, rgbDarkOrchid, " & _
        "rgbDarkRed, rgbDarkSalmon, rgbDarkSeaGreen, rgbDarkSlateBlue, rgbDarkSlateGray, rgbDarkTurquoise, rgbDarkViolet, rgbDeepPink, rgbDeepSkyBlue, " & _
        "rgbDimGray, rgbDodgerBlue, rgbFireBrick, rgbFloralWhite, rgbForestGreen, rgbFuchsia, rgbGainsboro, rgbGhostWhite, rgbGold, " & _
        "rgbGoldenrod, rgbGray, rgbGreen, rgbGreenYellow, rgbHoneydew, rgbHotPink, rgbIndianRed, rgbIndigo, rgbIvory, " & _
        "rgbKhaki, rgbLavender, rgbLavenderBlush, rgbLawnGreen, rgbLemonChiffon, rgbLightBlue, rgbLightCoral, rgbLightGoldenrodYellow, rgbLightGray, " & _
        "rgbLightGreen, rgbLightPink, rgbLightSalmon, rgbLightSeaGreen, rgbLightSkyBlue, rgbLightSlateGray, rgbLightSteelBlue, rgbLightYellow, " & _
        "rgbLime, rgbLimeGreen, rgbLinen, rgbMaroon, rgbMediumAquamarine, rgbMediumBlue, rgbMediumOrchid, rgbMediumPurple, rgbMediumSeaGreen, rgbMediumSlateBlue, " & _
        "rgbMediumSpringGreen, rgbMediumTurquoise, rgbMediumVioletRed, rgbMidnightBlue, rgbMintCream, rgbMistyRose, rgbMoccasin, rgbNavajoWhite, rgbNavyBlue, " & _
        "rgbOldLace, rgbOlive, rgbOliveDrab, rgbOrange, rgbOrangeRed, rgbOrchid, rgbPaleGoldenrod, rgbPaleGreen, rgbPaleTurquoise, rgbPaleVioletRed, " & _
        "rgbPapayaWhip, rgbPeachPuff, rgbPeru, rgbPink, rgbPlum, rgbPowderBlue, rgbPurple, rgbRed, rgbRosyBrown, rgbRoyalBlue, " & _
        "rgbSalmon, rgbSandyBrown, rgbSeaGreen, rgbSeashell, rgbSienna, rgbSilver, rgbSkyBlue, rgbSlateBlue, rgbSlateGray, rgbSnow, " & _
        "rgbSpringGreen, rgbSteelBlue, rgbTan, rgbTeal, rgbThistle, rgbTomato, rgbTurquoise, rgbYellow, rgbYellowGreen, rgbViolet, " & _
        "rgbWheat, rgbWhite, rgbWhiteSmoke", ",")

        
    For prettyColor = 0 To UBound(prettyColors)
        [A1].Cells(prettyColor + 1, 1).Interior.Color = prettyColors(prettyColor)
        [B1].Cells(prettyColor + 1, 1) = prettyNames(prettyColor)
    Next
End Sub

message edited by DerbyDad03


Reply ↓  Report •

Ask Question