Conditional formatting to highlight dates which are false

[xyncro]

I have a problem with how to apply a conditional formatting to a cell that contains a date I set.. because this specific date is controlled by a price I also set.

The steps how it's entered in the worksheet, and what I want to happen.. plz check the picture > https://1drv.ms/u/s!Av9VwR6S8vnqgQve...BalFl?e=mmjRnv

1. A date is first set (column V)
2. After that a price is set (column U)

If the price in column U corresponds to rows X and Y, then I don't want anything to happen.. but..
If the price in column U does not match rows X and Y, then I want that cell in column V with dates to be colored..

It's a way, a visual aid to get a warning when the right price is not correct..

Thx
/B

Image added by Moderator:
if-conditional-formatting.jpg

[6StringJazzer]

I think your explanation is missing something. Are the dates in column X the first date that the price in column Y becomes effective? I am going to assume yes.

Also, next time please attach your actual Excel file so we have data to work with. It's even easier than taking a screen shot and attaching the image. Few people will take the time to type all of your data from the image into a worksheet.

Let's try this. Conditional formatting rule for column V:

=U1=VLOOKUP(V1,X:Y,2,TRUE)

You will probably need this version

=U1=VLOOKUP(V1;X:Y;2;TRUE)

I do not know the Swedish words for VLOOKUP and TRUE.

[xyncro]

Hi 6stringjazzer.. and thx for your answer..

First..

I think your explanation is missing something. Are the dates in column X the first date that the price in column Y becomes effective? I am going to assume yes.

Absolutely correct..

And..

Also, next time please attach your actual Excel file so we have data to work with. It's even easier than taking a screenshot and attaching the image. Few people will take the time to type all of your data from the image into a worksheet.

I understand what you mean.. so I tried uploading my images several times.. with no success.. and then I tried once, twice again.. until my patience run out.. hence the "easy" link..

Now to this... problem..
My bad.. maybe I haven't been clear enough in my explanation.. so I want to rephrase my request..

It is a school template for ordering needles (N) and clamps (K) with names on..

This template has instructions here printed on A4 to how the xl template should be filled..

How the xl needs to be filled.. from left to right.. (* does not concern my request)

* 1. (column C) Names printed on the products.
* 2. (column D) Amount of needles (N) ordered.
* 3. (column E) Only an "N" entered to show its needles.
* 4. (column F) Color of the needles.
5. (column G) The price of the needle depended on what date it was ordered (column L) which checks what price is correct (column R-S 20-24).
* 6. (column H-I-J) basically the same as (column D-E-F) but with clamps (K) instead.
7. (column K) The price of the clamps depended on what date it was ordered (column L) which checks what price is correct (column R-T 20-24).
8. (column L) Order date for the products.
* 9. (column M) Delivery date.
* 10. (column N-O) Price each and sum.

Now.. what I basically want is.. when a wrong price is entered in columns G and K, then I want the cell with the date in column L to light up red, meaning the task is done wrong.
And the date in column L, as you mentioned in your answer, will decide the price from columns R-S-T 20-24.

I hope it makes better sense now..

Soo.. I think with conditional formatting maybe..?
Since neither of the cells can contain any formulas, except for the darker rows 7-13-19-25... and so on.. which will be locked.

Thx
/B

[6StringJazzer]

The merged cells in column L are a big problem. Excel experts recommend against them. If you used single cells and repeat the value, you could populate G and K by formula and they would be guaranteed to be correct, no need to check. See attached.

[xyncro]

Yes!! that's a way to do the job too..

I'm satisfied and thank you for the help..

I have only a tiny request though..

When I empty the cells in L column I get "checkered" cells on the same row..

Would it be possible to have empty cells in G and K columns when no date is entered in column L..?

Thx
/B

[6StringJazzer]

Just update the formula to this:

=IF(L2="","",VLOOKUP(L2,$R$21:$S$24,2,TRUE))