I believe I need VBA Coding, highlight cells that contain text

[LadyHarper]

ok if c3 contains text, highlight c3, c4, c5, c6, c7 light green, if c7 contains text override all cells c3, c4, c5, c6 and c7 to light red.

I need to be able to run this in all columns and each block of 5 cells... so d3, d4, d5, d6,d7 is a set and c8, c9, c10, c11, and c12 is a set and so on.

Basically an address is put in the first cell box, (I want all 5 cell boxes to turn light green) the next 2 boxes may contain info, and the 3'd box will contain info, and when a name is entered into the 5th box. Once a name is entered into the 5th box, I need all those same cells to turn light red.

I think this needs VBA coding but not sure.

Thanks

 

[sijpie]

You can do this in conditional formatting, but depending on how many rows these sets extend to it could become messy.
select rows 3 to 7
set up a new conditional format based on formula
enter: =ISTEXT(A$3)
then select formatting with light green
Now set up new conditional format based on formula
enter: =AND(ISTEXT(A$3),ISTEXT(A$7))
then select formatting with light red.
Repeat that for the remaining rows.

The above is preferable if the number of rows is not too large and in thos cases where you want to avoid macros in your workbook.


With a macro it would be easy to achieve regardless of the number of rows.

On the tab of the sheet (in Excel) right click and select 'View Code...'
In the window that opens paste this code.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Starting from row 3, colour blocks of 5 cells in column:
    ' light green if cell in row 3,8,13, etc contains text or
    ' light red if abave AND cell in row 7, 12, 17, etc contains text
    
    Dim lRi As Long, iM As Integer, iOffUp As Integer, iOffDown As Integer
    
    lRi = Target.Row
    If lRi < 3 Then Exit Sub    ' Ignore if changes in top two rows
    
    'get the top and bottom of the block in which changed cell sits
    lRi = lRi - 2
    iM = lRi Mod 5
    If iM > 1 Then Exit Sub     'ignore if cells between top or bottom of block changed
    
    'cells up to top of block:
    iOffUp = (lRi - 1) Mod 5
    iOffUp = iOffUp * -1
    'cells down to bottom of block
    iOffDown = (5 - iM) Mod 5
    
    ' set formatting
    With Target
        If Len(.Offset(iOffUp, 0)) And Not IsNumeric(.Offset(iOffUp, 0).Value) Then
            'top of block contains text
            If Len(.Offset(iOffDown, 0)) And Not IsNumeric(.Offset(iOffDown, 0).Value) Then
                'also bottom of block contains text -> colour red
                Range(.Offset(iOffUp, 0), .Offset(iOffDown, 0)).Interior.Color = RGB(256, 230, 230)
            Else
                'only top, colour green
                Range(.Offset(iOffUp, 0), .Offset(iOffDown, 0)).Interior.Color = RGB(230, 256, 230)
            End If
        Else
            'top does not contain text clear formatting
            With Range(.Offset(iOffUp, 0), .Offset(iOffDown, 0)).Interior
                .Pattern = xlNone
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
        End If
    End With
    
End Sub

That's it. This macro will now run automatically any time you make a change to the sheet.

You will need to save your workbook as macro enabled (.xlsm)