Conditional formatting

[bknight]

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.

[HansV]

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.

[bknight]

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.

What is the "--" in the formula?

[HansV]

It turns a string value such as "119" to the corresponding number value 119.

[bknight]

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?

[HansV]

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) ?

[bknight]

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

[HansV]

Sorry, I meant E2. Or should that be E2848?
Why two separate rules?

[bknight]

Sorry, I meant E2. Or should that be E2848?
Why two separate rules?

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.

[HansV]

What is the formula of the rule? It is only partly visible in your screenshot.

[bknight]

=OR(--LEFT(E2848,3)>140,--RIGHT(E2848,LEN(E2848)-FIND("/",E2848))>80)

[HansV]

That looks OK. Could you attach a stripped-down copy of the workbook, or send it to me?

[HansV]

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.

S2432.png

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.