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

Conditional Format for 4 condition

A

ananthram

New Member
Hi All,


i have below macro using. But what i need is instead of giving the keyword like Extreme, medium, low or something. can anyone please let me know how can i use aggregate percentage for each cell... like given on below given condition and i need to make 4 columns like a, b, c & d at once in single macro.


Condition:

[pre]
Code: 
(A+B) >= 90%      = Purple
90% >(A+B)>= 80%  = Green
80% >(A+B)>= 70%  = Yellow
(A+B) < 70%       = Orange
Macro:

Private Sub Worksheet_Change(ByVal Target As Range)
Set MyPlage = Range("A1:A1000")
For Each Cell In MyPlage

If Cell.Value = "Extreme" Then
Cell.Interior.ColorIndex = 3
End If
If Cell.Value = "High" Then
Cell.Interior.ColorIndex = 4
End If
If Cell.Value = "Medium" Then
Cell.Interior.ColorIndex = 18

End If
If Cell.Value = "Low" Then
Cell.Interior.ColorIndex = 6
End If

If Cell.Value <> "Extreme" And Cell.Value <> "High" And Cell.Value <> "Medium" And Cell.Value <> "Low" Then
Cell.Interior.ColorIndex = xlNone
End If

Next
End Sub
[/pre]
 
Just to clarify, are you wanting to use Conditional Formatting, or regular formatting that's triggered by your conditions in macro? Your current macro is the latter, so I wanted to clarify.

To get started though, here's a simple way to setup multiple criteria and output selections.

[pre]
Code: 
Sub FindChoice()
Dim MyValue As Long
Dim colorChoice As Long
MyValue = Range("A1") + Range("A2")
Select Case MyValue
Case Is >= 0.9
colorChoice = 3
Case Is >= 0.8
colorChoice = 4
Case Is >= 0.7
colorChoice = 18
Case Is < 0.7
colorChoice = 6
'and so on

Case Else
'If not one of other criteria
colorChoice = 0
End Select
Range("B2").Interior.ColorIndex = colorChoice
End Sub
[/pre]
 
Hi Sir,


Please check the below attached sample paper. and i need to apply condition(coloured) as given in my 1st post and that will get hightlighted on calculations + column E, F, G, H...


https://www.box.com/s/xqos9y3n84q4ojzzren6


like if A + B >= 90% then highlight E and F column in purple colour


so on with other conditions
 
Hi Ananth Ram ,


Try this :

[pre]
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
'           (A+B) >= 90%      = Purple
'           90% >(A+B)>= 80%  = Green
'           80% >(A+B)>= 70%  = Yellow
'           (A+B) < 70%       = Orange

Static MyPlage As Range
If MyPlage Is Nothing Then Set MyPlage = Range("E2:F20")            '  Change as required
If Application.Intersect(Target, MyPlage) Is Nothing Then Exit Sub

Dim cell As Range
Dim vbOrange As Long, vbPurple As Long
vbOrange = RGB(265, 165, 0)
vbPurple = RGB(160, 32, 240)

MyPlage.Resize(, 1).Select
For Each cell In Selection
A_val = cell.Value
B_val = cell.Offset(0, 1).Value
Select Case (A_val + B_val)
Case Is >= 90
cell.Resize(1, 2).Interior.Color = vbPurple
Case Is >= 80
cell.Resize(1, 2).Interior.Color = vbGreen
Case Is >= 70
cell.Resize(1, 2).Interior.Color = vbYellow
Case Else
cell.Resize(1, 2).Interior.Color = vbOrange
End Select
Next
Set MyPlage = Nothing
Target.Select
End Sub
[/pre]
Narayan
 
Hi Narayan Sir...


the above macro works only on A% and B% i need it for all A,B,C,D. and please let me know how to add different colours too.Thanks in advance.
 
Hi Ananth Ram ,


If you want that 4 cells added together should be used to decide on the conditional format , then try this :

[pre]
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
'           (A+B) >= 90%      = Purple
'           90% >(A+B)>= 80%  = Green
'           80% >(A+B)>= 70%  = Yellow
'           (A+B) < 70%       = Orange

Static MyPlage As Range
If MyPlage Is Nothing Then Set MyPlage = Range("E2:H20")            '  Change as required
If Application.Intersect(Target, MyPlage) Is Nothing Then Exit Sub

Dim cell As Range
Dim vbOrange As Long, vbPurple As Long
vbOrange = RGB(265, 165, 0)
vbPurple = RGB(160, 32, 240)

MyPlage.Resize(, 1).Select
For Each cell In Selection
A_D_vals = Application.WorksheetFunction.Sum(cell.Resize(, 4).Value)
Select Case A_D_vals
Case Is >= 90
cell.Resize(1, 4).Interior.Color = vbPurple
Case Is >= 80
cell.Resize(1, 4).Interior.Color = vbGreen
Case Is >= 70
cell.Resize(1, 4).Interior.Color = vbYellow
Case Else
cell.Resize(1, 4).Interior.Color = vbOrange
End Select
Next
Set MyPlage = Nothing
Target.Select
End Sub
If you wish to add more colors , you can use the following pre-defined color constants :


[b]vbRed , vbBlue , vbMagenta , vbCyan 


The colors [b]vbGreen[/b] and vbYellow[/b] , which I have already used , are also a part of the pre-defined color constants.


If you want to use colors other than these , just define them , and use them !


For example , if you want to use brown , you can use a declaration such as :

[pre][code]Dim vbBrown as Long
and a definition such as :

vbBrown = RGB(165,42,42)
[/pre]
and then use it in a statement such as :

cell.Resize(1, 4).Interior.Color = vbBrown[/code][/pre]
Check this link for the various colors , and their RGB values :


http://web.njit.edu/~kevin/rgb.txt.html


Of course , it makes sense to use additional colors only if you have more than 4 slabs ; please remember that too much of color actually makes it difficult on the eyes ; color sparingly used will highlight , but too much of it will create problems for users.


Narayan
 
no sir. even this last macro is not working on it


Scenario:

when A% and B% is applied with the condition then only those 2 columns should be higlighted and same case applies with C% and D%. condition is all we need to apply :(


Condition:

[pre]
Code: 
(A+B) >= 90%      = Purple
90% >(A+B)>= 80%  = Green
80% >(A+B)>= 70%  = Yellow
(A+B) < 70%       = Orange
[/pre]
 
Hi Ananth Ram ,


Are you saying that A and B should be considered separately , and similarly C and D should be considered separately ?


Narayan
 
Hi Ananth Ram ,


Try this :

[pre]
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
'           (A+B) >= 90%      = Purple
'           90% >(A+B)>= 80%  = Green
'           80% >(A+B)>= 70%  = Yellow
'           (A+B) < 70%       = Orange

Static MyPlage As Range
If MyPlage Is Nothing Then Set MyPlage = Range("E2:H20")            '  Change as required
If Application.Intersect(Target, MyPlage) Is Nothing Then Exit Sub

Dim cell As Range
Dim vbOrange As Long, vbPurple As Long
vbOrange = RGB(265, 165, 0)
vbPurple = RGB(160, 32, 240)

MyPlage.Resize(, 1).Select
For Each cell In Selection
A_B_vals = Application.WorksheetFunction.Sum(cell.Resize(, 2).Value)
Select Case A_B_vals
Case Is >= 90
cell.Resize(1, 2).Interior.Color = vbPurple
Case Is >= 80
cell.Resize(1, 2).Interior.Color = vbGreen
Case Is >= 70
cell.Resize(1, 2).Interior.Color = vbYellow
Case Else
cell.Resize(1, 2).Interior.Color = vbOrange
End Select
Next

MyPlage.Offset(0, 2).Resize(, 1).Select
For Each cell In Selection
C_D_vals = Application.WorksheetFunction.Sum(cell.Resize(, 2).Value)
Select Case C_D_vals
Case Is >= 90
cell.Resize(1, 2).Interior.Color = vbPurple
Case Is >= 80
cell.Resize(1, 2).Interior.Color = vbGreen
Case Is >= 70
cell.Resize(1, 2).Interior.Color = vbYellow
Case Else
cell.Resize(1, 2).Interior.Color = vbOrange
End Select
Next

Set MyPlage = Nothing
Target.Select
End Sub
[/pre]
Narayan
 
Narayan Sir ...It worked like a charm. Ty and one more request sir how can i give No fill in

[pre]
Code: 
Case Is >= 80%
cell.Resize(1, 4).Interior.Color = vbSGreen
[/pre]
?
 
Hi Ananth Ram ,


Please understand that the macro which I have posted need not be a Worksheet_Change event macro , since it is looking at the entire range , even when only one cell is changed ; you can use a much simpler macro , which looks at only the cell which has been changed along with its partner ; thus if E7 has been changed , it can look at E7 and F7 alone ; if G13 has been changed , it can look at G13 and H13 alone.


This will be a Worksheet_Change event macro , since every time any cell changes , it looks at only that cell , not the entire range.


If the posted macro does the job for you , I am OK with it ; if you feel the posted macro is slow because your data range is several thousand rows long , let me know , so that I can post the simplified macro.


Narayan
 
Hi Sir,


i understood the macro... But what my question is instead of colouring the cell is there an other option like
Code: 
no-fill(no colour) in Case Is
section
 
Hi Ananth Ram ,


Replace the following 2 statements in your code :

[pre]
Code: 
Case Is >= 70
cell.Resize(1, 2).Interior.Color = vbOrange
by the following 4 statements :

Case Is >= 60
cell.Resize(1, 2).Interior.Color = vbOrange
Case Else
cell.Resize(1, 2).Interior.Color = xlNone
[/pre]
The pre-defined constant xlNone stands for no color.


Of course , you can replace any color value such as vbPurple , vbGreen , vbYellow , vbOrange by xlNone , if you want NO FILL for that particular band of values.


Narayan
 
You must log in or register to reply here.
Back
Top