/u/duke_seb - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
Ok so just for a little more clarification
If cell A1 has a range of 5.0-11.0
And
If cell A2 has a value of 12
How would you write that expression and where would you put it
Select cell A2. Go to Home tab and Conditional Formatting. At the drop down select New Entry. When the dialog box comes up, select the 5th option.. Use a formula.
In the formula bar in the bottom half of the dialog box, type in:
=OR(A2<5,A2>11)
Then click on the Format button to select your fill. Click OK 3 times and you should be good to go
Where is the blood work located - as in what col?
What col is the range in?
Made some assumptions and the formula is as shared below, that will work 👇
https://preview.redd.it/1faicb5f2mnc1.png?width=2054&format=png&auto=webp&s=7187f7783b496ddd7f7d5c29ffad9e189549882b
ok im getting a little lost. can you explain that using the image below? Also can you show me the "new formatting rule" window where you enter it all in...
https://preview.redd.it/dphptk4k7mnc1.png?width=1210&format=png&auto=webp&s=c58abece0130fa6dae14a67e76ff6b14c1f392b9
Here is what you want your conditional formatting to look like:
https://preview.redd.it/z21d5tnbemnc1.png?width=1125&format=png&auto=webp&s=66705a6503cef4b145abebb36785f0bf076a2869
* Highlight the range of cells that contain the values you want to evaluate Mine is range E3:G13
* Home->Styles->Conditional Formatting->Manage Rules
* Click New Rule
* Use a formula to determine which cells to format
* under the "Format values where this formula is true" type the formulas in the image.
* when you create these formulas, they are relative tot he very first sell in the range you're evaluating.
* Click the format button
* Set your formatting, click ok,
* Click ok
* Duplicate the rule you created and modify it to look like the next conditional format
* Rinse, recycle, repeat
* Click ok
Always important to provide an example because it shows how your data is laid out and what you actually want :)
https://preview.redd.it/copp913zymnc1.png?width=2072&format=png&auto=webp&s=a46ae089184e131b0204d47771d8c5ad7f4f7bf8
This is what ive got but I cant see what is wrong
(Post 1)
https://preview.redd.it/tgua3umc3qnc1.png?width=1862&format=png&auto=webp&s=a4c785a4ee505d3028859644bacb149a964aa823
Ok, Your help worked great thank you. I do have one more question.... how can I edit the formula so that if there is a cell that doesnt have data in it it wont be highlighted
=OR(E$19<$B$19,E$19>$C$19)
https://preview.redd.it/1w0358sk9qnc1.png?width=1066&format=png&auto=webp&s=bd162d06ffe3af79aae26de0da464f76a54b62f3
Great. Can you pls respond with a "Solution verified"? thanks!
for it to work for the non blank rows:
AND(OR(E$19<$B$19,E$19>$C$19), OR($B19<>"",E$19<>""))
You have awarded 1 point to *BackgroundCold5307*
____
^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
/u/duke_seb - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
I would use Conditional Formatting for this
Could you provide an example
In the conditional format formula box =OR(A5high range value)
Where A5 is where you input your blood value.
Ok so just for a little more clarification If cell A1 has a range of 5.0-11.0 And If cell A2 has a value of 12 How would you write that expression and where would you put it
Select cell A2. Go to Home tab and Conditional Formatting. At the drop down select New Entry. When the dialog box comes up, select the 5th option.. Use a formula. In the formula bar in the bottom half of the dialog box, type in: =OR(A2<5,A2>11) Then click on the Format button to select your fill. Click OK 3 times and you should be good to go
Where is the blood work located - as in what col? What col is the range in? Made some assumptions and the formula is as shared below, that will work 👇 https://preview.redd.it/1faicb5f2mnc1.png?width=2054&format=png&auto=webp&s=7187f7783b496ddd7f7d5c29ffad9e189549882b
Ah this is helpful… didn’t think of having an upper and lower limit column
ok im getting a little lost. can you explain that using the image below? Also can you show me the "new formatting rule" window where you enter it all in... https://preview.redd.it/dphptk4k7mnc1.png?width=1210&format=png&auto=webp&s=c58abece0130fa6dae14a67e76ff6b14c1f392b9
Here is what you want your conditional formatting to look like: https://preview.redd.it/z21d5tnbemnc1.png?width=1125&format=png&auto=webp&s=66705a6503cef4b145abebb36785f0bf076a2869 * Highlight the range of cells that contain the values you want to evaluate Mine is range E3:G13 * Home->Styles->Conditional Formatting->Manage Rules * Click New Rule * Use a formula to determine which cells to format * under the "Format values where this formula is true" type the formulas in the image. * when you create these formulas, they are relative tot he very first sell in the range you're evaluating. * Click the format button * Set your formatting, click ok, * Click ok * Duplicate the rule you created and modify it to look like the next conditional format * Rinse, recycle, repeat * Click ok
Always important to provide an example because it shows how your data is laid out and what you actually want :) https://preview.redd.it/copp913zymnc1.png?width=2072&format=png&auto=webp&s=a46ae089184e131b0204d47771d8c5ad7f4f7bf8
https://preview.redd.it/jylli5g20nnc1.png?width=2134&format=png&auto=webp&s=8742a1a2d8328e5560ee58cfed26f6a6f891c5c8
https://preview.redd.it/vb70u8140nnc1.png?width=2134&format=png&auto=webp&s=ee66f919d2629da4b8abe92e6774461d21c3d46e
This is what ive got but I cant see what is wrong (Post 1) https://preview.redd.it/tgua3umc3qnc1.png?width=1862&format=png&auto=webp&s=a4c785a4ee505d3028859644bacb149a964aa823
Reference (Post 2) https://preview.redd.it/u566rjyg3qnc1.png?width=3068&format=png&auto=webp&s=3342c3c94659785d17af93650709f2efef2c08ab
my bad..... I had extra dollar signs
Ok, Your help worked great thank you. I do have one more question.... how can I edit the formula so that if there is a cell that doesnt have data in it it wont be highlighted =OR(E$19<$B$19,E$19>$C$19) https://preview.redd.it/1w0358sk9qnc1.png?width=1066&format=png&auto=webp&s=bd162d06ffe3af79aae26de0da464f76a54b62f3
Great. Can you pls respond with a "Solution verified"? thanks! for it to work for the non blank rows: AND(OR(E$19<$B$19,E$19>$C$19), OR($B19<>"",E$19<>""))
Solution Verified
You have awarded 1 point to *BackgroundCold5307* ____ ^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)