• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 330
  • Last Modified:

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

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
Fordraiders
Asked:
Fordraiders
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
armchair_scouseCommented:
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
 
armchair_scouseCommented:
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
 
FordraidersAuthor Commented:
ok where is the :
 'Applies to' part of the rule.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
gowflowCommented:
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
 
redmondbCommented:
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
 
armchair_scouseCommented:
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
 
FordraidersAuthor Commented:
Thanks to all
0
 
redmondbCommented:
Thanks, fordraiders.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now