Excel macro help

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.

Thanks
LVL 5
magentoAsked:
Who is Participating?
 
Rgonzo1971Commented:
Hi,

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
Next

End Sub

Open in new window

Regards EDIT Variable number of cols
0
 
Rgonzo1971Commented:
No attachment
0
 
Rgonzo1971Commented:
HI,

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

Regards
0
 
magentoAuthor Commented:
Sorry , Please find the attachment .
plot-ee.xlsx
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.

All Courses

From novice to tech pro — start learning today.