Conditional Formatting with IF Statement

Hi Walt, welcome to the forum.

If I understand you correctly, you have TWO values in A2? e.g. "10 to 20" or "10-20"?

If that is the case, can you split it into two cells? We can probably work around it if you can't, but it would make the conditional formatting formula shorter.

=AND(A3>=A2,A3<=B2)

If A2 has "10-20" as a range, the CF formula would be something like:

=AND(A3>=LEFT(A2,FIND("-",A2)-1)+0,A3<=MID(A2,FIND("-",A2)+1,LEN(A2))+0)

Hi Paul

If I understand you correctly, you have TWO values in A2? e.g. "10 to 20" or "10-20"?

-Four which is dependent on what is selected from A1.
--IF A1 is 1st Plate, A2 is 15-20 ASF
--IF A1 is 2nd Plate, A2 is 7-12 ASF
--IF A1 is 3rd Plate, A2 is 20-25 ASF
-IF A2 is 15-20 ASF, A3 is green when number entered in A3 is between 15-20. A3 is red if number entered in A3 is not between 15-20.

Ok, set the normal background color of cell A3 to red. Add the following Conditional Formatting formula to cell A3:

=AND(A3>=LEFT(A2,FIND("-",A2)-1)+0,A3<=SUBSTITUTE(MID(A2,FIND("-",A2)+1,LEN(A2))," ASF","")+0)

Set the conditional formatting Fill color to green. This way, if the cell is empty or does not fall in the range from A2, it will be red.

Click "Conditional Formatting" toolbar button > click "New Rule..." > click "Use a formula to determine which cells to format"

Enter the formula provided into the "Format values where this formula is true:" textbox below that. Click the "Format..." button to set the Font/Fill colors. Click OK twice to return to the worksheet.

The formula works for all conditions, as long as A2 is in the format "XX-YY ASF".

I tell myself every day that I'm a genius. Doesn't impress people at all.

I learned a lot about Excel just by playing around, Googling, and by reading many of the amazing posts by folks who are far more genius than I. Many (if not most) of my fellow moderators are Microsoft MVP's in Excel (or should be if they aren't).

I'm just glad I can help when possible.

Same to you, Walt.

I don't plan on getting in line outside Best Buy at 6pm (or earlier) Thanksgiving evening in the small chance of getting a TV, though.

Original Formula:

=AND(A3>=LEFT(A2,FIND("-",A2)-1)+0,A3<=SUBSTITUTE(MID(A2,FIND("-",A2)+1,LEN(A2))," ASF","")+0)

The AND() function tests each of the conditions within and returns TRUE or FALSE. All must be TRUE in order for the entire function to be TRUE. In my AND formula above there are two conditions.

First Condition: A3>=LEFT(A2,FIND("-",A2)-1)+0

FIND("-",A2) finds the position of the first hyphen in cell A2. This hyphen will be to the right of your first number. It then subtracts 1 from that position to exclude the hyphen in the LEFT function.

LEFT(A2,FIND("-",A2)-1)+0 uses that position-1 as the number of characters from the left you want to return. It then adds 0 to that "number" so it is converted to a real number and not just a text string representation of a number.

=LEFT("10-15 ASF",FIND("-","10-15 ASF")-1)+0 becomes
=LEFT("10-15 ASF",3-1)+0
=LEFT("10-15 ASF",2)+0
="10"+0
=10

The condition test then compares A3 to that value, and if A3 is greater than or equal to that value, then the condition returns TRUE.

Second condition: A3<=SUBSTITUTE(MID(A2,FIND("-",A2)+1,LEN(A2))," ASF","")+0

The MID function, MID( string , start position , length ), also looks for the first hyphen (notice the same FIND function) but then ADDS one spot to that position so that it starts returning text from one position to the right of that hyphen. The LEN() function at the end tells it to return as many characters exist in that cell. (Even though we're not starting at the first character, if you tell the MID function to return 20 characters in a cell only containing 15, you will only get a max of 15 characters, not 15 characters plus five spaces.)

=MID(A2,FIND("-",A2)+1,LEN(A2)) becomes
=MID("10-15 ASF",FIND("-","10-15 ASF")+1,LEN("10-15 ASF")
=MID("10-15 ASF",3+1,9)
=MID("10-15 ASF",4,9)
=15 ASF

The MID function is then wrapped in a SUBSTITUTE function to eliminate the " ASF" portion of the string and just return the second number (15). SUBSTITUTE( original text , text to be replaced , text to replace with )

SUBSTITUTE("15 ASF"," ASF","") means substitute the " ASF" in "15 ASF" with nothing (an empty string). We then add 0 to the remaining string, "15", to convert it to a numeric value (15).

A3 is once again compared with this second value, and if A3 is less than or equal to the value TRUE is returned. If either condition fails (returns FALSE), the entire function will return FALSE and not apply the conditional formatting.

Hopefully this helps you and others who are looking to do the same or similar tasks.

When do your classes start? I swear I need to attend one.
This is very helpful. Thank you so much for the explanation. I'll send you actual calculator when I'm done with it.

and...I decided not to go to Best Buy and wait in line at 6pm, I'm going to Fry's and wait in line at 7pm!!!!
Happy Holidays,
Walter V.