Solved

Color Cells in a Row Columns B to O If they are Blank or Empty

Posted on 2013-01-28
8
315 Views
Last Modified: 2013-01-31
Excel 2010 vba.

I need a function that will Color a Row "Yellow"  if the cells are Blank or Empty from for that row from Column "B" TO "O"..


Thanks
fordraiders
0
Comment
Question by:fordraiders
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 9

Expert Comment

by:armchair_scouse
Comment Utility
Presuming you have Excel 2010...

Highlight cells B to O on your worksheet... then go to Home tab on your ribbon bar...  select Conditional Formatting... New Rule...  Select rule type 'Format only cells that contain'...  Change the dropdown that says 'Format only cells with:' and select 'Blanks' as choice...  then click on Format, select Fill tab, choose a Yellow background.
You should end up with the rule you need.  You can extend it to different rows by changing the 'Applies to' part of the rule.

HTH
0
 
LVL 9

Expert Comment

by:armchair_scouse
Comment Utility
And the VBA recorded version of that was....

     Range("B3:P3").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=LEN(TRIM(B3))=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

Open in new window

0
 
LVL 3

Author Comment

by:fordraiders
Comment Utility
ok where is the :
 'Applies to' part of the rule.
0
 
LVL 29

Assisted Solution

by:gowflow
gowflow earned 250 total points
Comment Utility
Is that what you want ??
I hv extended this formating for the first 200+ lines but you simply take the last row yellow and drag it down as you wish.

My unbderstanding is the following
For a specific row if ALL cells for that row are blank then the row (B to O) is yellow.
If ANY of the cells is not blank then the whole row (B to O) is then no color or no yellow !

Try it and let me know.
gowflow
FormatYellowWhite.xlsx
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 26

Accepted Solution

by:
redmondb earned 250 total points
Comment Utility
Hi fordraiders.

I'm not sure exactly what you wanted, so the attached has two versions...
Sheet1
Select cells from one or more rows and press the blue button. For each of the selected rows, if all the cells in columns B to O are blank then they're all coloured yellow, otherwise all of their colours are turned off.
Sheet2
Similar to Sheet1 except that Conditional Formatting is used (and so rows don't need to be selected.)

Sheet1's macro is as follows...
Option Explicit

Sub Colour_Row()
Dim xCell  As Range
Dim xRange As Range
Dim xBlank As Boolean
Dim i      As Long

Sheets("Sheet1").Activate

If TypeName(Selection) <> "Range" Then
    MsgBox ("Current selection type is " & TypeName(Selection) & " - run cancelled.")
    Exit Sub
End If

Set xRange = Intersect(Range("A:A"), Selection.EntireRow)

For Each xCell In xRange
    xBlank = False
    For i = 1 To 14
        If xCell.Offset(0, i) <> "" Then Exit For
        If i = 14 Then xBlank = True
    Next
    If xBlank Then
        xCell.Offset(0, 1).Resize(1, 14).Interior.Color = 65535
    Else
        xCell.Offset(0, 1).Resize(1, 14).Interior.Pattern = xlNone
    End If

Next

End Sub

Open in new window

Regards,
Brian.Colour-Row.xlsm
0
 
LVL 9

Expert Comment

by:armchair_scouse
Comment Utility
Hi fordraiders,

Some of the posts subsequent to mine might suit your needs better, but in response to your question, the 'Applies To' bit is from the Selection.  The first line of the code selects B3:P3, and then the subsequent bits of code work on 'Selection'.

HTH
0
 
LVL 3

Author Closing Comment

by:fordraiders
Comment Utility
Thanks to all
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Thanks, fordraiders.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

763 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

9 Experts available now in Live!

Get 1:1 Help Now