conditional formatting

Jammydan

Board Regular
Joined
Feb 15, 2010
Messages
141
Apologies as i know this question gets asked alot, but i can't find a suitable answer.

I am using conditional formating to highlight a row based on entries from A1 to S500.

=COUNTIF($N15,"App Recd/In Progress")=1
=COUNTIF($N15,"Opened")=
1
=COUNTIF($N15,"Declined")=1


excel is restricted to 3 conditions and I need a further 2 , can anybody supply some sample code to do this in a macro?

Many thanks
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Assuming your data is entered manually (Dropdown?) and you are looking at Column "N", in the worksheet module
Rich (BB code):
Private Sub WorkSheet_Change(ByVal Target as Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 14 Then Exit Sub
Dim iCol as Variant
Select Case Target
Case "App Recd/In Progress":iCol = 6
Case "Opened": iCol =4
Case "Declined" : iCol = 8
'etc
Cae Else: iCol = xlNone
End Select
Target.Interior.ColorIndex = iCol
End Sub
lenze
 

Jammydan

Board Regular
Joined
Feb 15, 2010
Messages
141
Hi, I managed to do what I wanted with the following code.
Code:
' Private Sub Worksheet_Change(ByVal Target As Range)Define the range to check
Set ColourMagic = Range("C5:C500")
' Start checking each cell in the target range named ColorMagic and nothings
For Each Cell In ColourMagic
If Cell.Value = "Opened" Then ' Color it light green
Range("B" + CStr(Cell.Row) + ":S" + CStr(Cell.Row)).Interior.ColorIndex = 35
ElseIf Cell.Value = "Declined" Then ' Color it coral
Range("B" + CStr(Cell.Row) + ":S" + CStr(Cell.Row)).Interior.ColorIndex = 22
ElseIf Cell.Value = "App Recd/In Progress" Then ' Color it ice blue
Range("B" + CStr(Cell.Row) + ":S" + CStr(Cell.Row)).Interior.ColorIndex = 24
ElseIf Cell.Value = "Not Proceeding" Then ' Color it tan
Range("B" + CStr(Cell.Row) + ":S" + CStr(Cell.Row)).Interior.ColorIndex = 40
ElseIf Cell.Value = "Pending" Then ' Color it ivory
Range("B" + CStr(Cell.Row) + ":S" + CStr(Cell.Row)).Interior.ColorIndex = 19
Else ' Remove all color
Range("A" + CStr(Cell.Row) + ":S" + CStr(Cell.Row)).Interior.ColorIndex = xlNone
End If
Next
End Sub

Cheers
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690

ADVERTISEMENT

Why are you using a Change Event?. I don't understand what you are doing!! Why are you looping through the entire range??
Is this what you are trying to do??
When a cell in C5:C500 is changed or data entered, check the value and color that row from Columns "B:S" the correct Color.
Or, do you need to loop through exising values??
lenze
 

Jammydan

Board Regular
Joined
Feb 15, 2010
Messages
141
Basically, I have a speadsheet which various users will complete regarding opening an account. Depending on what stage the account opening process is at eg, "received", "opened", "declined", "pending" or "not proceeding" then the row will be a certain colour. Also if it is declined or not proceeding, it creates a report sheet. The full code is below. If you like, I will happily send you a copy of the spreadsheet.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 And LCase(Target.Value) = "declined" Then
    Application.EnableEvents = False
    With Target.EntireRow.Copy
    Sheets("Declined Cover Sheet").Visible = True
    Sheets("Declined Cover Sheet").Select
    ActiveSheet.Range("C3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Selection.HorizontalAlignment = xlLeft
    Sheets("Declined Cover Sheet").Range("A1").Select
    ActiveSheet.PageSetup.PrintArea = "$B$2:$C$23"
    End With
    Application.EnableEvents = True
    Application.Dialogs(xlDialogPrint).Show
    Sheets("Declined Cover Sheet").Visible = False
    Sheets("Master Sheet").Select
End If
If Target.Column = 3 And LCase(Target.Value) = "not proceeding" Then
    Application.EnableEvents = False
    With Target.EntireRow.Copy
    Sheets("Not Proceeding Cover Sheet").Visible = True
    Sheets("Not Proceeding Cover Sheet").Select
    ActiveSheet.Range("C3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Selection.HorizontalAlignment = xlLeft
    Sheets("Not Proceeding Cover Sheet").Range("A1").Select
    ActiveSheet.PageSetup.PrintArea = "$B$2:$C$23"
    End With
    Application.EnableEvents = True
    Application.Dialogs(xlDialogPrint).Show
    Sheets("Not Proceeding Cover Sheet").Visible = False
    Sheets("Master Sheet").Select
End If
' Define the range to check
Set ColourMagic = Range("C5:C500")
' Start checking each cell in the target range named ColorMagic and nothings
For Each Cell In ColourMagic
If Cell.Value = "Opened" Then ' Color it light green
Range("B" + CStr(Cell.Row) + ":S" + CStr(Cell.Row)).Interior.ColorIndex = 35
ElseIf Cell.Value = "Declined" Then ' Color it coral
Range("B" + CStr(Cell.Row) + ":S" + CStr(Cell.Row)).Interior.ColorIndex = 22
ElseIf Cell.Value = "App Recd/In Progress" Then ' Color it ice blue
Range("B" + CStr(Cell.Row) + ":S" + CStr(Cell.Row)).Interior.ColorIndex = 24
ElseIf Cell.Value = "Not Proceeding" Then ' Color it tan
Range("B" + CStr(Cell.Row) + ":S" + CStr(Cell.Row)).Interior.ColorIndex = 40
ElseIf Cell.Value = "Pending" Then ' Color it ivory
Range("B" + CStr(Cell.Row) + ":S" + CStr(Cell.Row)).Interior.ColorIndex = 19
Else ' Remove all color
Range("A" + CStr(Cell.Row) + ":S" + CStr(Cell.Row)).Interior.ColorIndex = xlNone
End If
Next
End Sub
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
I see what you are doing and if it works, don't fix it. You should, however consider using Select Case instead of the If's EsleIf's. Also, why not just color the row when you do the Copy Paste 'etc
Code:
ActiveSheet.PageSetup.PrintArea = "$B$2:$C$23"
Cells(Target.Row,"B").Resize(1,18).Interior.ColorIndex = 35
End With

lenze
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,920
Messages
5,834,375
Members
430,282
Latest member
MeredithD26

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top