Multiple search criteria to force action

atmwillow

New Member
Joined
Dec 1, 2015
Messages
4
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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.
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
Something like this??

Code:
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
 
Upvote 0
The easiest would be to write a formula at the top and then do a conditional format.

If you had a table like this:
AB
1TRUE
2Pos 1M
3Pos 2
4Pos 3O
5Pos 4O
6Pos 5
7Pos 6A
8Pos 7
9Pos 8
10Pos 9TS
11Pos 10TD

<tbody>
</tbody>


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
 
Upvote 0
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!
LgcmUCr
 
Upvote 0
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!
LgcmUCr
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top