Conditional Formatting

A

aravindvin

Excel provides only 3 levels of conditional foramtting.
Using
Format>conditional formatting

I need to have more than three.

Please advise as to how I can do this. I do not know VB script.

Would greatly appreciat if you can send a sample with brief.

Thanks
Aravind:confused
 
A

aravindvin

Please find attached the dummy excel to view the requirement.

Requirement:::
The color of cells should change based on the values in two other cell
(importance and rating).

Importance: This value determines the number of colored cells
Rating: This value determines the color of the cells

Pls. let me know how we can do this in VB or conditional formatting.
(requires more than 3 conditional formats)

Thanks
Aravin

Attachment filename: worksheet.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=65897
 
J

JE McGimpsey

Sorry - I don't open unsolicited workbooks (virii, for one thing, and
long experience that people usually attach workbooks because they're too
lazy to clearly articulate their request, for another).

I also really would rather not respond to those that are unwilling to
help themselves by looking in the archives when they've been pointed to
them, much less those who simply repeat requirements, rather than
explaining why a technique suggested in a previous reply won't work...

Perhaps someone else will be more generous.
 
D

David McRitchie

Hi Arvind,

I looked at your workbook, and because you do not give a
description in text of what you want, I will have to describe
in text what you wanted, but more directed to what this
Event Macro will be doing.

Bar Graph composed of cells, for a severity ranked by color

Your spreadsheet has entry in columns 8 an 9 (H & I)
Column H provides a number 0 to 4 (though you white out 5)
Column I provides a number 1 to 5 to create a color (colorindex used)

Columns 2:6 are colored from the left for a length of what is
in Column H, for a color derived from Column I (col 9).

ColorIndex values can be found in
http://www.mvps.org/dmcritchie/excel/colors.htm
1=Black, 2=White, 3=Red, 6=Yellow, 10=dark Green, 46 Orange

Use of Event macros can be found in
http://www.mvps.org/dmcritchie/excel/event.htm

Install by rightclick on the worksheet tab, view code

Rather than using .clear the interior.colorindex is set to white
which clears out grid lines, which it appears you did not want to see.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 8 Or Target.Column > 9 Then Exit Sub 'H & I
If Target.Row < 2 Then Exit Sub
Dim R As Long, I As Long
R = Target.Row
If Not IsNumeric(Cells(R, 8) & Cells(R, 9)) Then Exit Sub
I = Application.Choose(Cells(R, 9).Value, 1, 3, 46, 6, 10)
Intersect(Rows(Target.Row), Columns("B:F")).Interior.ColorIndex = 2
Range("B" & R & ":" & Cells(R, 1 + _
Cells(R, 8).Value).Address(0, 0)).Interior.ColorIndex = I
End Sub

By not describing what you want you put an extra burden on
anyone who wants or tries to help. By not doing this effort yourself
you imply to others that you don't really care enough to describe
the problem in plain text so why should anyone try to help you.
And I think the lack of using your full name when corresponding
also indicates a further disregard to others in the newsgroup,
and in your own contribution.

If your 38KB file were actually an attachment it would have made
your posting over 80K. Fortunately it is not an attachment but
is a file on a website. But for anybody to see what the question
is you make them look at the file, and the newsgroup archives
http://google.com/advanced_group_search?q=group:*Excel*&num=100
will not show what the question is so it would not help others with
similar questions who were willing to search archives for something close to what they want. Also see
http://www.mvps.org/dmcritchie/excel/xlnews.htm
 
K

kvedogg

David,

I'm new to this bulletin board and don't know the most effective way to get
a response to a post. I've posted this question somewhere on the bulletin
board but after reading a number of your responses, I thought you might be
able to help me. Here's my problem:

I'd like to format cells in Excel for 10 different text conditions. At the
simple end I can format a cell by saying Cell Value is Equal To ="John". I
can do that for 3 different names. I can even trick the system by using Cell
Value is Between ="John" and ="Mike" and have 3 different formats for 6
conditions. How would I use conditional formatting to for example, turn the
cell blue for a John, Mike or Sara entry, green for a Dick or Mary entry and
red for a Jane, Bob, Sally or Paul entry?

If I have to use a macro to accomplish this, how would I do it?

Any help is appreciated.


David McRitchie said:
Hi Arvind,

I looked at your workbook, and because you do not give a
description in text of what you want, I will have to describe
in text what you wanted, but more directed to what this
Event Macro will be doing.

Bar Graph composed of cells, for a severity ranked by color

Your spreadsheet has entry in columns 8 an 9 (H & I)
Column H provides a number 0 to 4 (though you white out 5)
Column I provides a number 1 to 5 to create a color (colorindex used)

Columns 2:6 are colored from the left for a length of what is
in Column H, for a color derived from Column I (col 9).

ColorIndex values can be found in
http://www.mvps.org/dmcritchie/excel/colors.htm
1=Black, 2=White, 3=Red, 6=Yellow, 10=dark Green, 46 Orange

Use of Event macros can be found in
http://www.mvps.org/dmcritchie/excel/event.htm

Install by rightclick on the worksheet tab, view code

Rather than using .clear the interior.colorindex is set to white
which clears out grid lines, which it appears you did not want to see.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 8 Or Target.Column > 9 Then Exit Sub 'H & I
If Target.Row < 2 Then Exit Sub
Dim R As Long, I As Long
R = Target.Row
If Not IsNumeric(Cells(R, 8) & Cells(R, 9)) Then Exit Sub
I = Application.Choose(Cells(R, 9).Value, 1, 3, 46, 6, 10)
Intersect(Rows(Target.Row), Columns("B:F")).Interior.ColorIndex = 2
Range("B" & R & ":" & Cells(R, 1 + _
Cells(R, 8).Value).Address(0, 0)).Interior.ColorIndex = I
End Sub

By not describing what you want you put an extra burden on
anyone who wants or tries to help. By not doing this effort yourself
you imply to others that you don't really care enough to describe
the problem in plain text so why should anyone try to help you.
And I think the lack of using your full name when corresponding
also indicates a further disregard to others in the newsgroup,
and in your own contribution.

If your 38KB file were actually an attachment it would have made
your posting over 80K. Fortunately it is not an attachment but
is a file on a website. But for anybody to see what the question
is you make them look at the file, and the newsgroup archives
http://google.com/advanced_group_search?q=group:*Excel*&num=100
will not show what the question is so it would not help others with
similar questions who were willing to search archives for something close to what they want. Also see
http://www.mvps.org/dmcritchie/excel/xlnews.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

JE McGimpsey said:
Sorry - I don't open unsolicited workbooks (virii, for one thing, and
long experience that people usually attach workbooks because they're too
lazy to clearly articulate their request, for another).
Click to expand...
Click to expand...
 
D

David McRitchie

If you just have 3 color choices per cell then you can use conditional
formatting. There are lots of examples and lots of instructions
on what to do on my Conditional Formatting
page. http://www.mvps.org/dmcritchie/excel/condfmt.htm

These are worksheet functions and most worksheet functions are
not case sensitive so that makes things easy.

For either of two names:
=OR(A1="John", "Mike")

For a range between "John" and "Mike" inclusive
=AND(A1>="John", A1<="Mike")

For a range between first letter "J" through first letter "M"
=AND(LEFT(A1,1)>="J", LEFT(A1,1)<="M")
 
Top