VBA Conditional Formating - Cell 1 format based on Cell2 <= other worksheet cell3

[john12345678]

Hello good people...
I'm at my wit's end!

All I want to do is the following:
Make Cell 1 turn red if it is less than, or equal to Cell 2 (which is on another worksheet).

Specifically
Active worksheet cell A4 is to have a RED background, if cell D4 is <= N10 on worksheet "FORMULAS"

I need it to act as if it were a normal conditional format (alter values in D4 and the color in A4 will turn red as described if the value becomes less or equal to N10 on the other worksheet called "Formulas"). As long as the value of D4 is greater than the value of N10 (in the formulas worksheet) then the background color should remain as-is (regardless of color). It should also return to that color if it has been red at some point (IE the stock increases past the value of N10).


THAT'S IT AND THAT'S ALL! I would use the function in excel but I have to repeat the procedure for literally 1000 rows (since its for an inventory stock list with 1000 items - and yes I will need to use a procedure trick to get around the length of the repeated code, but I can handle that... all I need is the procedure itself).

PLEASE HELP!

Best regards
John

[p45cal]

A4's conditional formatting formula:
=D4<=FORMULAS!$N$10
presuming that N10 is the cell that all rows are looking at.

[john12345678]

A4's conditional formatting formula:
=D4<=FORMULAS!$N$10
presuming that N10 is the cell that all rows are looking at.

I appreciate the responce, but this is only part of the code I would need to make the conditional formatting work in VBA.


N10 is actually not the same for each line...
A4 changes based on whether D4<=N10
but
A5 changes based on whether D5<=N47

Long story short - the N cell reference is moved 37 spaces down everytime A&D cells go one space down.

I intend to write the code for a single line, and copy it 1000 times (while increasing A and D by one and altering the "N" value by 37 every time).

[p45cal]

When you're putting the conditional formatting in place, first select the cells to be conditionally formatted, starting from D4 (so that cell remains the active cell) then go into conditional formatting, delete any related legacy conditional formats then use this formula:

=$D4<=INDEX(FORMULAS!$N:$N,(ROW($D4)-4)*37+10)This will look at cells in rows 10,47,84,121,158,195,232,269,306 etc. in column N of the FORMULAS sheet.

[john12345678]

When you're putting the conditional formatting in place, first select the cells to be conditionally formatted, starting from D4 (so that cell remains the active cell) then go into conditional formatting, delete any related legacy conditional formats then use this formula:

=$D4<=INDEX(FORMULAS!$N:$N,(ROW($D4)-4)*37+10)This will look at cells in rows 10,47,84,121,158,195,232,269,306 etc. in column N of the FORMULAS sheet.

The code doesn't work.
I think there is one bracket too many.

In addition, I cannot check entire rows.. there is a lot of information not relevant to the format in other cells on the same rows.

The formula has to be specific for A4's background color - based on whether D4 is less than or equal to the value in N10 (N10 is, as stated, on another worksheet called "formulas").

I appreciate any help I can get on this matter.

best regards
john

[p45cal]

I checked it before posting, I've double checked just now. Works fine.
What do you mean by "cannot check entire rows"? Do you want to highlight more than one cell in the row or do you want to check more cells in a row against a cell in column N of the FORMULAS sheet?

[john12345678]

I checked it before posting, I've double checked just now. Works fine.
What do you mean by "cannot check entire rows"? Do you want to highlight more than one cell in the row or do you want to check more cells in a row against a cell in column N of the FORMULAS sheet?

Hi,

No. I don not want to highlight more than one Cell. But it looks like the formula is trying to do that, or search the whole row (but I could be reading it wrong and since I can't get it to work then that might be the issue).

I want to change the background color of every single cell between A4 and A1000 (independently), based on whether D4 to D1000 (Independently) is less than or equal to their counterparts (which are 37 cells apart) in "Formulas" N10 to N36862.

Example:
A4 (regular color) because D4 > "formulas!N10"

At the samte time
A5 (red) because D5 <= "formulas!N47"

At the same time
A6 (regular color) because D6 > "formulas!N84"

etc.

[p45cal]

I don not want to highlight more than one Cell. But it looks like the formula is trying to do that, or search the whole row (but I could be reading it wrong and since I can't get it to work then that might be the issue). What cells get highlighted in this case is what cells are included in the Applies to field:
27571
All the rest is being carried out as you've stated. It's looking at single cells on the FORMULAS sheet, not whole rows.
Supply a file (bare bones if it sensitive, of just the relevant areas and sheets) with your not-working conditional formatting, stating your version of Excel and I'll try to fix it.
27572

[john12345678]

What cells get highlighted in this case is what cells are included in the Applies to field:

All the rest is being carried out as you've stated. It's looking at single cells on the FORMULAS sheet, not whole rows.
Supply a file (bare bones if it sensitive, of just the relevant areas and sheets) with your not-working conditional formatting, stating your version of Excel and I'll try to fix it.



2757327574



The failure messege basically says that the code is incorrect (Norwegian version.... I'm a proud Norwegian, but seriously... why the h"#ll do we even make EXCEL in Norwegian... EVERYONE HERE SPEAKS ENGLISH AS A SECOND LANGUAGE!) #code errors #menu names.

Microsoft Office Professional Plus 2016 (excel appears to be 2016 as well).

[p45cal]

I can't see the pictures clearly enough, resolution too low. A file would be much better. It would translate automatically back to Norwegian.
In the meantime

=$D4<=INDEKS(FORMULAS!$N:$N;(RAD($D4)-4)*37+10)?Just a guess.

[john12345678]

I can't see the pictures clearly enough, resolution too low. A file would be much better. It would translate automatically back to Norwegian.
In the meantime

=$D4<=INDEKS(FORMULAS!$N:$N;(RAD($D4)-4)*37+10)?Just a guess.

............. ok... I am so happy I am about to loose my cool and yell so loud the entire office will hear me... because you appear to be a genius.. and this appears to be working EXACTLY as intenden.... BUT.. before I completely loose my S"#7 and call it a day... I need to know exactly WHY this is working.
That is, can you PLEASE explain the code so someone who has been trying to figure this out for two weeks now (namely me...) can understand it?

I need to know why its working (aka how) so that I can fix it if something goes wrong.

WITH ENORMOUS GRATITUDE AND MASSIVE RESPECT!
John

[p45cal]

See attached with formula breakdown. If you can't understand still then come back.

[john12345678]

THANK YOU!!
Honestly, this was a 10/10! THANK YOU for giving me the tools I needed to finish a document I have been working on every day for alomst a month... thanks to you its FINALLY completed!
I can't wait to put it to good use!.

THANK YOU!!:clap::clap:

[john12345678]

PS: how do I mark this thread as "SOLVED"?

[p45cal]

PS: how do I mark this thread as "SOLVED"?

http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#faq_new_faq_item2