Sub routine to test and color cells [Closed]

Report
-
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
-
Hello,
I am trying to develop a simple function or sub to scan a worksheet of approx 2000 cells, test the contents, if the cell is empty leave, if the cell contains the value 1 leave, and color everything else (e.g incorrect answers) red.

Its been a while since I programmed in BASIC

Many Tahnks

Ian

2 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
800
your data is from A1 down in column A

try this macro ( modify to suit you)

Sub test()
Dim rng As Range, c As Range
Set rng = Range(Range("A1"), Cells(Rows.Count, "A").End(xlUp))
For Each c In rng
If c <> "" And c <> 1 Then c.Interior.ColorIndex = 3
Next c
End Sub
Thanks for that venkat

However I could not get it to go past the "If c <> line..."

This is a typical column

a
c
c
1
d
a
0
b

I only want to leave 1 and 0. All other answers )a,b,c,d) to be coloured red

Would this command be useful to determine the spreadsheet size i.e. avoid counting column by column?

Worksheets(1).Range("a1:k50")

Hope you can help, again!

Thanks

Ian
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
800
I do not understand this did not work. What do you mean that it stops at C<> line.
it works i n my computer
one moe thing in your first message said that "if the cell is empty leave". but the cell is not empty but has zero(0) in the cell.

so change
c<>""
into
c<>0
check again

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!