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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

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
Back
Top