Multiple search criteria to force action

[atmwillow]

Hi,
I am attempting to write a macro that checks a column for multiple criteria and if one is missing then sets cell 1 in the column to red.
(It is for a roster to ensure all positions are filled, eg there is an "M", an "A", two "O"s, a "TS" or a "DS" and keep the first cell in that row red until all the required positions are filled)
It would need to run separately for each column (each one is a different day of the month)


I would very much appreciate any help on this!

Note: using excel 2003

Cheers

 

[thes4s67]

Let me try to understand what you want.. So you have a COLUMN. You want a macro search through that COLUMN and if it does not find something, make the first cell red?

If you could provide some sample data... I'm better at visualizing then reading.

 

[atmwillow]

Hi, that is basically it in a nutshell!

I'm trying to add an image but it only gives me a URL link box?

Basic premis, it is a roster, has all the names in Column A, All the days of the month along Row 1.
For each day (which is a column) I need to fill say 12 positions (shifts) at my work (each identified by a letter eg "M" or "O" or "TS"). I would like the macro to check to to see if I rostered enough people for the day by ensuring I have assigned all of those 12 values in each column.

Does that make sense?

 

[AngelEG]

Does it have to be using a macro? You could probably get away with a couple of formulas.

To post pictures, use an external image service, like Imgur: The most awesome images on the Internet, you can upload the image there and it will give you a link to the image that can be used on your post. You don't need to subscribe to imgur to upload and get links to images.

Hope it helps and you can post an image of what you're trying to do.

Cheers

Angel

Hi, that is basically it in a nutshell!

I'm trying to add an image but it only gives me a URL link box?

Basic premis, it is a roster, has all the names in Column A, All the days of the month along Row 1.
For each day (which is a column) I need to fill say 12 positions (shifts) at my work (each identified by a letter eg "M" or "O" or "TS"). I would like the macro to check to to see if I rostered enough people for the day by ensuring I have assigned all of those 12 values in each column.

Does that make sense?

 

[thes4s67]

Something like this??

Sub sample()
Dim rng As Range

'Change Range to your range
Set rng = Range("B2:E17")


For Each cell In rng.Cells
    If cell.Value = "" Then
    cell.Interior.ColorIndex = 3
    End If
Next


End Sub

 

[AngelEG]

The easiest would be to write a formula at the top and then do a conditional format.

If you had a table like this:
[TABLE="width: 159"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD="align: center"]TRUE
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Pos 1[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Pos 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Pos 3[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Pos 4[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Pos 5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Pos 6[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Pos 7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Pos 8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Pos 9[/TD]
[TD]TS[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Pos 10[/TD]
[TD]TD[/TD]
[/TR]
</tbody>[/TABLE]


The formula on B1 would is TRUE only if you have 1 M, 2 O's, 1 A and (1 TS or 1 TD).

The formula is: =AND(COUNTIF(B2:B11,"M")=1,COUNTIF(B2:B11,"A")=1,COUNTIF(B2:B11,"O")=2,OR(COUNTIF(B2:B11,"TS")=1,COUNTIF(B2:B11,"DS")=1))

Then using conditional formating, if B1=FALSE, then change color to red, and copy the formating to the rest of the different cells.

Hope it helps.

Cheers,

Angel

Hi,
I am attempting to write a macro that checks a column for multiple criteria and if one is missing then sets cell 1 in the column to red.
(It is for a roster to ensure all positions are filled, eg there is an "M", an "A", two "O"s, a "TS" or a "DS" and keep the first cell in that row red until all the required positions are filled)
It would need to run separately for each column (each one is a different day of the month)


I would very much appreciate any help on this!

Note: using excel 2003

Cheers

 

[atmwillow]

Hey guys, thanks for your help!
Unfortunately, I think with yours thes4s67 is that there will always be empty cells somewhere which would make it always red?
AngelED, I think yours would work, could I make it check if the letter it found was capital or not? (Also, I have two positions called "O", might be easier to change the letters....)

I have actually attached a link for visualization, sorry for the delay, couldn't do it at work!

 

[AngelEG]

Hi atmwillow,

Unfortunatelly COUNTIF does not make a difference between capital or lower-case.

The formula I posted actually looks for 2 O's, so the count for O's must be 2 otherwise you'll get a FALSE for the resulting formula; you can always change the letter if you want of course.

=AND(COUNTIF(B2:B11,"M")=1,COUNTIF(B2:B11,"A")=1,COUNTIF(B2:B11,"O")=2,OR(COUNTIF(B2:B11,"TS")=1,COUNTIF(B2:B11,"DS")=1))

Cheers,

Angel

Hey guys, thanks for your help!
Unfortunately, I think with yours thes4s67 is that there will always be empty cells somewhere which would make it always red?
AngelED, I think yours would work, could I make it check if the letter it found was capital or not? (Also, I have two positions called "O", might be easier to change the letters....)

I have actually attached a link for visualization, sorry for the delay, couldn't do it at work!

 

[atmwillow]

This method seems to be working very well Angel, thankyou!

 

[AngelEG]

I'm glad I could help out.

This method seems to be working very well Angel, thankyou!