Solved

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

Posted on 2013-01-28
8
321 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 9

Expert Comment

by:armchair_scouse
ID: 38826712
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
ID: 38826727
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
ID: 38826755
ok where is the :
 'Applies to' part of the rule.
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 30

Assisted Solution

by:gowflow
gowflow earned 250 total points
ID: 38826836
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
 
LVL 26

Accepted Solution

by:
redmondb earned 250 total points
ID: 38826840
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
ID: 38831898
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
ID: 38838290
Thanks to all
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38839474
Thanks, fordraiders.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

729 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