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
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,895
Messages
5,514,016
Members
408,982
Latest member
Cooksey19

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top