Highlight cells

Posted on 2011-04-19
Last Modified: 2012-05-11
If there is a value in Column A.  How can I hightlight (turn Red) all cells that are empty or has a value of 0 or less
Question by:cd_morris
    LVL 85

    Expert Comment

    by:Rory Archibald
    You can use conditional formatting, but we'd need more information: which cells do you want to highlight based on which cell(s) in column A having a value?
    LVL 4

    Author Comment

    Can it be done with VBA?
    LVL 4

    Author Comment

    So while there is a value in column A, and if a cell in range B:I is either blank or has a value of ) or less, I want to turn it red
    LVL 85

    Accepted Solution

    Something like this - right-click the sheet tab, choose View Code and paste this in:
    Private Sub Worksheet_Change(ByVal Target As Range)
       Dim rngCell As Range, rngMonitor As Range, rngSubCell
       On Error GoTo err_handle
       ' adjust range to monitor
       Set rngMonitor = Range("A:I")
       With Application
          .ScreenUpdating = False
          .EnableEvents = False
       End With
       If Not Intersect(Target, rngMonitor) Is Nothing Then
          For Each rngCell In Intersect(Target, rngMonitor)
             If Len(Cells(rngCell.Row, "A").Value) > 0 Then
                If rngCell.Column = 1 Then
                   For Each rngSubCell In Range(Cells(rngCell.Row, "B"), Cells(rngCell.Row, "I"))
                      If Len(rngSubCell.Value) = 0 Or rngSubCell.Value < 0 Then rngSubCell.Interior.ColorIndex = 3
                   Next rngSubCell
                   If Len(rngCell.Value) = 0 Or rngCell.Value < 0 Then rngCell.Interior.ColorIndex = 3
                End If
             End If
          Next rngCell
       End If
       With Application
          .EnableEvents = True
          .ScreenUpdating = True
       End With
       Exit Sub
       MsgBox Err.Description
       Resume clean_up
    End Sub

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now