conditional formatting? i want to highlight the cell below based on contents above

I have a list like this:

D6915
D6915-07
D6915 - 05b
D6915 - 05a
D6915 - 05
D6914 - 07
D6915 - 03

except that the list contains 50,000 rows. I want to highlight every cell below the one that ends in -07, so in this case, I would highlight the one that ends in 05b and 03.

Someone please please help me with this. I am giving myself carpal tunnel doing this by hand

Hi there,

The following macro should do the trick - just make any adjustments as noted in the code:

Sub ShadeParticularCells()

Dim lngLastRow As Long

'Find the last row and assign it to the 'lngLastRow' variable _
based on Column A (change as required).
lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row

'Assumes the data is in Column A and starts at row 1 (change as required).
For Each cell In Range("A1:A" & lngLastRow)
    'If the current cell has '07' in it, then...
    If InStr(cell, "07") Then
        '...highlight the one beneath it in green (change colour to suit).
        cell.Offset(1, 0).Interior.ColorIndex = 10
    End If
Next cell

End Sub

HTH

Robert

Thank you very much for your quick response!

Is there a way to modify the formulas you guys gave me to highlight cells with -06, -05, OR -04 and only if there is no highlighting in there already?

And for some reason, when I asked the original question, I thought it was easy to sort cells by highlighting color. How can I do that?



Thank you thank you

Hi there,

The amended code shades the cells as requested - just change the colour to suit.

There's no inherent function to sort by colour in Excel, but fortunately this User Defined Function (UDF) does the job.

HTH

Robert

Sub ShadeParticularCells()

Dim lngLastRow As Long

'Find the last row and assign it to the 'lngLastRow' variable _
based on Column A (change as required).
lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row

'Assumes the data is in Column A and starts at row 1 (change as required).
For Each cell In Range("A1:A" & lngLastRow)

    'If the current cell has '04' in it, then...
    If InStr(cell, "04") > 0 Then
        'Check if the active cell has any shading. _
        If it doesn't then...
        If cell.Interior.ColorIndex = xlNone Then
            '...shade the cell in yellow (change colour to suit).
            cell.Interior.ColorIndex = 6
        End If
    End If
    
    'If the current cell has '05' in it, then...
    If InStr(cell, "05") > 0 Then
        'Check if the active cell has any shading. _
        If it doesn't then...
        If cell.Interior.ColorIndex = xlNone Then
            '...shade the cell in orange (change colour to suit).
            cell.Interior.ColorIndex = 46
        End If
    End If
    
    'If the current cell has '06' in it, then...
    If InStr(cell, "06") > 0 Then
        'Check if the active cell has any shading. _
        If it doesn't then...
        If cell.Interior.ColorIndex = xlNone Then
            '...shade the cell in blue (change colour to suit).
            cell.Interior.ColorIndex = 5
        End If
    End If
    
    'If the current cell has '07' in it, then...
    If InStr(cell, "07") > 0 Then
        'Check if the cell beneath it has any shading. _
        If it doesn't and it's not blank (empty), then...
        If cell.Offset(1, 0).Interior.ColorIndex = xlNone And _
            IsEmpty(cell.Offset(1, 0)) = False Then
            '...shade the cell in green (change colour to suit).
            cell.Offset(1, 0).Interior.ColorIndex = 10
        End If
    End If

Next cell

End Sub

You saved me a lot of time and effort and I appreciate it so much!