Conditional formatting
-
- BronzeLounger
- Posts: 1412
- Joined: 08 Jul 2016, 18:53
Conditional formatting
I have a spreadsheet that I have referenced before. It takes blood pressure readings and parses the string into numbers. The fill color of the cell is updated due to medication changes. I supply this spreadsheet to the doctors cut I would like to tweak a bit I would like the fill color to change to red if(value(left,Cell,3)>140 or value(right,Cell,2)>80). This could be a conditional format, but doesn't need to be.
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Conditional formatting
Select the range you want to format this way. The top left cell of the range should be the active cell in the selection.
In the following, I'll use D2 as the address of this cell. Change the formula for your situation.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula
=OR(--LEFT(D2,3)>140,--RIGHT(D2,2)>80)
Click Format...
Activate the Fill tab.
Select red as highlight color.
Click OK, then click OK again.
In the following, I'll use D2 as the address of this cell. Change the formula for your situation.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula
=OR(--LEFT(D2,3)>140,--RIGHT(D2,2)>80)
Click Format...
Activate the Fill tab.
Select red as highlight color.
Click OK, then click OK again.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1412
- Joined: 08 Jul 2016, 18:53
Re: Conditional formatting
What is the "--" in the formula?HansV wrote: ↑18 Aug 2023, 13:42Select the range you want to format this way. The top left cell of the range should be the active cell in the selection.
In the following, I'll use D2 as the address of this cell. Change the formula for your situation.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula
=OR(--LEFT(D2,3)>140,--RIGHT(D2,2)>80)
Click Format...
Activate the Fill tab.
Select red as highlight color.
Click OK, then click OK again.
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Conditional formatting
It turns a string value such as "119" to the corresponding number value 119.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1412
- Joined: 08 Jul 2016, 18:53
Re: Conditional formatting
OK, well the unintended consequences struck me again. The spreadsheet has formulas that are filled in automatically when the systolic/diastolic are added to the cells that have the conditional formatting. When I copied the cell formatting all the cells were filled with green. there were no data just the conditional formatting. With nulls in the cells, the green fill was defaulted. Is there a way to have the cells just excel defualt?
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Conditional formatting
What is the formula in D2?
Do you have more conditional formatting rules than the one with formula =OR(--LEFT(D2,3)>140,--RIGHT(D2,2)>80) ?
Do you have more conditional formatting rules than the one with formula =OR(--LEFT(D2,3)>140,--RIGHT(D2,2)>80) ?
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1412
- Joined: 08 Jul 2016, 18:53
Re: Conditional formatting
I didn't include column D as it is blank. No other formula in conditional formatting other than what you suggested in the second post.
Manage conditional rules for 20 Aug 23
Manage conditional rules for 20 Aug 23
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Conditional formatting
Sorry, I meant E2. Or should that be E2848?
Why two separate rules?
Why two separate rules?
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1412
- Joined: 08 Jul 2016, 18:53
Re: Conditional formatting
That was what manage conditional formats looked like in edit format. Subsequently, I deleted the lower rule. Here is todays edit rules image, no 2nd rule. These were all copied and paste special>formats from E2846. The format worked as the rules were not met and the fill color remained green. Now as to why the copied cell has a range, I don't know the is what the copy special formats gives.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Conditional formatting
What is the formula of the rule? It is only partly visible in your screenshot.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1412
- Joined: 08 Jul 2016, 18:53
Re: Conditional formatting
=OR(--LEFT(E2848,3)>140,--RIGHT(E2848,LEN(E2848)-FIND("/",E2848))>80)
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Conditional formatting
That looks OK. Could you attach a stripped-down copy of the workbook, or send it to me?
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Conditional formatting
You have directly applied green as fill color to E2443:E2874. If you don't want to fill empty cells green, use conditional formatting for that too.
I created two rules for E2443:E4000 - that should be sufficient for a long time.
The bottom one colors non-blank cells green.
The top one colors cells with high blood pressure red. Since it its listed above the other one, it has precedence.
Since the range now begins in E2443, the formula of the rule for red is
=OR(--LEFT(E2443,3)>140,--RIGHT(E2443,LEN(E2443)-FIND("/",E2443))>80)
I'll return the workbook by email.
I created two rules for E2443:E4000 - that should be sufficient for a long time.
The bottom one colors non-blank cells green.
The top one colors cells with high blood pressure red. Since it its listed above the other one, it has precedence.
Since the range now begins in E2443, the formula of the rule for red is
=OR(--LEFT(E2443,3)>140,--RIGHT(E2443,LEN(E2443)-FIND("/",E2443))>80)
I'll return the workbook by email.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans