Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Highlight cells

Posted on 2011-04-19
Medium Priority
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
  • 2
  • 2
LVL 85

Expert Comment

by:Rory Archibald
ID: 35424808
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?

Author Comment

ID: 35424824
Can it be done with VBA?

Author Comment

ID: 35424853
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

Rory Archibald earned 2000 total points
ID: 35424906
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


Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

571 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