Excel macro help

Posted on 2015-02-18
Last Modified: 2015-03-04
Hi ,

I am working on a build sheet where we will be doing checks manually now.
I need your help to automate the 3 tasks.

1. Spell check
2. Highlight rows which dont have any plots. (In the attached row 4 has no plots)
3. Highlight columns which have unique plots.(in the attached column F and H has unique plots)

For spell check , i find the below macro online from excel tricks. but i need your help on adding the task2 and 3 with this macro.
Sub ColorMispelledCells()
For Each cl In ActiveSheet.UsedRange
If Not Application.CheckSpelling(Word:=cl.Text) Then _
cl.Interior.ColorIndex = 28
Next cl
End Sub

The attached is only sample and the column/row may be varied.

Question by:magento
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
LVL 51

Expert Comment

ID: 40618266
No attachment
LVL 51

Expert Comment

ID: 40618292

pls try

For Each rw In ActiveSheet.UsedRange.Rows
    Debug.Print Application.CountA(rw)
    If Application.CountA(rw) = 0 Then
        rw.Interior.ColorIndex = 28
    End If
Next rw
For Each cl In ActiveSheet.UsedRange.Columns
    If Application.CountA(cl) = 1 Then
        cl.Interior.ColorIndex = 28
    End If
Next rw

Open in new window


Author Comment

ID: 40618304
Sorry , Please find the attachment .
LVL 51

Accepted Solution

Rgonzo1971 earned 500 total points
ID: 40618329

pls try

Sub Macro()

Set myRange = Range(Range("F3"), Cells(Range("E" & Rows.Count).End(xlUp).Row, Cells(1, Columns.Count).End(xlToLeft).Column))
For Each rw In myRange.Rows
    Debug.Print Application.CountA(rw)
    If Application.CountA(rw) = 0 Then
        rw.Interior.ColorIndex = 20
    End If
Next rw
For Each cl In myRange.Columns
    If Application.CountA(cl) = 0 Then
        cl.Interior.ColorIndex = 20
    End If

End Sub

Open in new window

Regards EDIT Variable number of cols

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel VBA Workbook - Change hours 2 41
EXCEL String-handling question ... 7 50
Create email hyperlinks from regular text 3 27
Cannot locate cell 15 41
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
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…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

737 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