Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-01-28
8
Medium Priority
?
323 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
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!

 
LVL 31

Assisted Solution

by:gowflow
gowflow earned 1000 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 1000 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

721 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