Validating cells based on content and background color

Sheets("Sheet1").range("A:DD").Interior.ColorIndex = 6 -> That won't work unless the entire range shares the same color. What you are going to have to do, is either check each cell individually OR come up with an alternative (like below).


1)
Question - how are you finding the cells to turn yellow in the first place? - You could a named formula to remember that range (call it required_cells or something).
Then your final check will be much easier as you can just check that range - still one cell at a time, but you will greatly reduce the number of cells to check.

2)
Use some kind of special cell to hold the number of required cells (like off to the side, call it 'RequiredCount' or something). 'RequiredCount' will hold a value, say 10 for example. Use another cell, call it 'CountActual' to hold something like =COUNTA(A1, B3, Y7...) where those made up addresses are the actual references to the required cells (you'll have to populate these special cells when you make the required cells yellow).

IF those 2 cells don't equal each other - there is missing information from one or more required cells... Have your before save macro just check the numbers.

Make sense?

Does this do what you are looking for?

Please Login or Register  to view this content.

Hi GeneralDisarray,

Listed below is some code that changes the interior.colorindex to yellow based on a couple of conditions:

Please Login or Register  to view this content.

Depending on the value of column 94 and 95, the interior.colorindex could be black, yellow or xlNone. If it's black or xlNone, entry shouldn't be required. Only if there is a yellow background.

In your first suggestion, what would the named formula look like?

Thanks, so much, for your help!

What about my code didn't work? I tested it on the attached file and it seems to work.

Sorry. There was one line that only worked because of the way my sheet was set up. This should be better:

Please Login or Register  to view this content.

The problem is the code Nigel posted doesn't match the code in the workbook he attached:

Posted:

Please Login or Register  to view this content.

What was actually in the 'test()' sub:

Please Login or Register  to view this content.

That's why it was only checking the 1st row - but if you used what he posted it would only check the last row.


You can just use the "UsedRange" method - you don't actually need to count it's rows - which will save you the need for DIM statements (and I don't think the WITH was needed):

Please Login or Register  to view this content.

SEE ATTACHED

Again - this is not the best way to go, but if you want to actually check each cell in the used range for the conditions listed - that will do it.

Thank you General. I wasn't sure exactly how to use the usedrange method in this case. I didn't want to use the regular methods for finding the last row because I don't know where the data would be on the sheet and most only look at column A. Thanks for teaching me something today!

@nigel - no problem, and right-back-at-cha on previous postings you've shared.

@OP

Yeah, it wasn't terribly slow and may do the trick for you completely. I shy away from "just loop over every cell in the worksheet" methods as much as possible.

I occurred to me while I was typing that previous sentence - that you could just check the blank cells in the used range, which would be less iterations through the loop.


Adjusted code - just checks the blank cells in the usedRange to see if they are yellow:

Please Login or Register  to view this content.

I also added a 'cell.select' line to take the user to the required cells that caused the routine to exit in the first place.