Solved

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

Posted on 2013-01-28
8
316 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
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
 
LVL 29

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
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…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

930 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

16 Experts available now in Live!

Get 1:1 Help Now