Conditional Formatting - IF Function

**thecat131** · 12-27-2011, 10:04 PM

Hi there
I've attached a snippet of a spreadsheet I use at work. It normally has 300+ patients listed and I need to do two things

1. Find a way to highlight any rows that do not have any data in columns F thru to S (we've not booked another appointment for these patients) and then copy those rows ( A thru to S) into either another worksheet or workbook (so we can check the patient file for the next appointment or discharge the patient).

2. Find a way to highlight any rows where the data in columns F thru to S has passed and then copy those rows (A thru to S) into either another worksheet or workbook.

Any ideas would be much appreciated.

Cheers
thecat131

**protonLeah** · 12-28-2011, 01:46 AM

The Conditional formula

=COUNTIF($F2:$S2,"<"&TODAY())

highlights any row with an expired date
the formula

=COUNT($F2:$S2)=0

highlights rows with no dates entered
This macro will copy not move the specified data to separate sheets:

Sub PatientStatus()
    Dim TestBlock   As String, _
        Cel         As Range, _
        CurrentRow  As Long
        
    For Each Cel In Range("B2:B" & Cells(Rows.Count, 2).End(xlUp).Row)
        TestBlock = Cel.Offset(0, 4).Resize(, 14).Address(0, 0)
        
        'check for any date in range
        If WorksheetFunction.Count(Range(TestBlock)) = 0 Then
            CurrentRow = Sheets("No Date").Cells(Rows.Count, 2).End(xlUp).Row + 1
            Sheets("no date").Range("A" & CurrentRow & ":S" & CurrentRow).Value = Cel.Offset(0, -1).Resize(, 19).Value
        
        'check for expired any expired date
        
        ElseIf WorksheetFunction.CountIf(Range(TestBlock), "<" & Date) > 0 Then
            CurrentRow = Sheets("expired").Cells(Rows.Count, 2).End(xlUp).Row + 1
            Sheets("expired").Range("A" & CurrentRow & ":S" & CurrentRow).Value = Cel.Offset(0, -1).Resize(, 19).Value
        End If
    Next Cel
End Sub