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

LadyHarper

New Member
Joined
Jun 28, 2017
Messages
44
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
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.
VBA 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)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,206
Messages
5,600,302
Members
414,375
Latest member
Onmyown

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
Top