First, thanks for reading my post!
So here goes, I'm trying to setup either a formula or macro, whichever would work best, in which if two conditions hold true, both based off a partial text search function, a validation alert comes up for that cell. The conditions vary. So as an example:
if cell A2 contains "Meeting"
and cell C2 contains "Foyer"
Change cell C2's color to pink and add a validation alert to cell C2 saying "Change to "Meeting"
Or
if cell A3 contains "Ballroom"
and cell C3 contains "Meeting"
Change cell C3's color to pink and add a validation alert to cell C3 saying "Change to "Ballroom".
I've gotten the color to change using conditional formatting with an "And" function combined with a "Search" function, but I don't know how to get the validation alert added to the cell. I was thinking maybe I could run a validation rule based off any cells that are the specific color that they would change to, but that doesn't appear to be a validation option. Also, the validation text alert would vary depending on the circumstance as illustrated in my example above, where in the first case, the alert says to change it to "Meeting" and in the second case, it says to switch it to "Ballroom". This is typically dependent on whatever is found in column A, so perhaps another if statement could be setup whereby:
if a cell in column c is pink, the alert would state "Change to Meeting", but only if the word "Meeting" is what is found through a partial text search in column A. And if "Ballroom" was found in column A, it would state "Change to Ballroom".
Once this is built out, I then want to report on how many times I've provided the validation alert across a specific range. I'm thinking I can use a CountIf formula or something similar to do that, but let me know if you agree.
Thanks so much for your help!
So here goes, I'm trying to setup either a formula or macro, whichever would work best, in which if two conditions hold true, both based off a partial text search function, a validation alert comes up for that cell. The conditions vary. So as an example:
if cell A2 contains "Meeting"
and cell C2 contains "Foyer"
Change cell C2's color to pink and add a validation alert to cell C2 saying "Change to "Meeting"
Or
if cell A3 contains "Ballroom"
and cell C3 contains "Meeting"
Change cell C3's color to pink and add a validation alert to cell C3 saying "Change to "Ballroom".
I've gotten the color to change using conditional formatting with an "And" function combined with a "Search" function, but I don't know how to get the validation alert added to the cell. I was thinking maybe I could run a validation rule based off any cells that are the specific color that they would change to, but that doesn't appear to be a validation option. Also, the validation text alert would vary depending on the circumstance as illustrated in my example above, where in the first case, the alert says to change it to "Meeting" and in the second case, it says to switch it to "Ballroom". This is typically dependent on whatever is found in column A, so perhaps another if statement could be setup whereby:
if a cell in column c is pink, the alert would state "Change to Meeting", but only if the word "Meeting" is what is found through a partial text search in column A. And if "Ballroom" was found in column A, it would state "Change to Ballroom".
Once this is built out, I then want to report on how many times I've provided the validation alert across a specific range. I'm thinking I can use a CountIf formula or something similar to do that, but let me know if you agree.
Thanks so much for your help!
