Data validation macro

[matpj]

Hi everyone,

I need to create a macro that validates data in a worksheet.
I have created a buton that will be pressed by the user, but the code is where I have problems.

there are numerous checks I need the macro to make - and then highlight cells that have a problem.

The macro will need to look at each row in turn from the row 2 to the end of the data..

an example of a check would be, checking if columns 1, 2, 3, 4 or 5 have a blank value in.
If so then highlight the cells red.

another check is to look at column 2 - if it contains "G13" and column 5 contains "DST01UK" or "INTERUK" then highlight the cell in column 5 orange...

I think if I can work out how to do these two examples I will be able to then do the rest of them

can anyone help me out with this - as I have NO idea how to start.... (or finish!)

thanks in advance,
Matt

[colofnature]

Here you go:

sub validate_data()
dim c as range
' Make blanks in columns A thru E red
for each c in intersect(range("a:e"),activesheet.usedrange)
if c.value = empty then c.interior.colorindex = 3
next
' Make col E orange if...
for each c in intersect(columns("b"),activesheet.usedrange)
if ucase(c.value = "G13") and (ucase(c.offset(,4).value) _
= "DST01UK" or ucase(c.offset(,4).value) = "INTERUK") _
then c.offset(,4).interior.colorindex = 45
next
end sub


HTH
Col

[matpj]

thats great, thank you!

I've been trying to adjust this code so that it starts from Row 2
but cant seem to do this (as I have also added some code to remove the red if the cell subsequently has a value)

also the main data I want to validate stops before the end of UsedRange so the validation needs to go from row 2 down to 1 row above wherever column K starts with the string "Sub Total"

does that make sense?

is this do-able?!

thanks again,
Matt

[colofnature]

Replace:

for each c in intersect(range("a:e"),activesheet.usedrange)

with:

for each c In intersect(range("a:e"), range(rows(2), _
columns("k").find(what:="sub total", lookat:=xlpart). _
offset(-1, 0)).entirerow)


C

[matpj]

hello again.
hopefully this will be the last time I need to query your brain!

I have been adapting yoru code to suit for my various validations.

I have the following but it does not seem to do what is intended:

Please Login or Register  to view this content.

This is supposed to check column K.
If there is a value of 100 in there, it then checks to see if column B has a value of "Des" and also checks the value of coumn G to see what is written in there.
If there is anything other than "Prepare Design" then I want to shade column G in ColorIndex 4 (lime green!!)

can you see why my code doesn't do anything?

thanks,
Matt

[colofnature]

Change:
c.Offset(, -4).Value = "Prepare Design"

to:
c.Offset(, -4).Value <> "Prepare Design" _
Then c.Offset(, -4).Interior.ColorIndex = 4


C

[matpj]

thats great!
I DO actually have one more question.

for the first validation where I just check rows a:e I need to skip an entire row where the first 6 characters of column a are equal to:
D01530
D01532
D01533
D01534

I have tried several things but i'm just making a mess of it.
I have used your code to complete most of my remaining validations with great success.
I owe you one!

cheers,
Matt

[colofnature]

No worries, just change:
if c.value = empty then c.interior.colorindex = 3

to:
if c.value = empty and not (c.column=1 and _
(left(c.value = "D01530" or _
left(c.value = "D01532" or _
left(c.value = "D01533" or _
left(c.value = "D015304"))) _
then c.interior.colorindex = 3


C

[matpj]

Hi,

my formula now looks like this:
For Each c In Range([A2], Columns("K:K").Find(What:="Sub Total").Offset(-2))
If c.Value = Empty And Not (c.Column = 1 And _
(Left(c.Value = "D01530") Or _
Left(c.Value = "D01532") Or _
Left(c.Value = "D01533") Or _
Left(c.Value = "D015304"))) _
Then c.Interior.ColorIndex = 3 Else c.Interior.ColorIndex = 0

Next

I get an error saying "Argument Not Optional" and it focuses on the first "Left"....
any ideas??

[colofnature]

Sorry, I was in a hurry and made a bit of a mess of that ilne - the dangers of just typing this stuff without checking that it'll actually work!

The non-optional argumant is the numer of characters in the Left(...) command, and the line should look like this:

For Each c In Range([A2], Columns("K:K").Find(What:="Sub Total").Offset(-2))
If c.Value = Empty And Not (c.Column = 1 And _
(Left(c.Value,6) = "D01530" Or _
Left(c.Value,6) = "D01532" Or _
Left(c.Value,6) = "D01533" Or _
Left(c.Value,6) = "D015304")) _
Then c.Interior.ColorIndex = 3 Else c.Interior.ColorIndex = 0

C

[matpj]

Hi Col,

I have used this, and it processes without error, but doesn't seem to skip the entire row if the value in Column A corresponds to one on the list..

it still gos through and highlights the empty rows on that line in Red..

any ideas?

Thanks,
Matt