Multiple Conditional Formatting

A

AbrahamGluck

Board Regular
Joined
Apr 12, 2016
Messages
129
Office Version
  1. 365
Platform
  1. Windows
[TABLE="class: grid, width: 300, align: center"]
<tbody>[TR]
[TD="align: center"]Symbol[/TD]
[TD="align: center"]Price[/TD]
[TD="align: center"]Strike[/TD]
[TD="align: center"]Type[/TD]
[/TR]
[TR]
[TD]AAPL[/TD]
[TD]$95.00[/TD]
[TD]$85.00[/TD]
[TD]Put[/TD]
[/TR]
[TR]
[TD]AAPL[/TD]
[TD]$95.00[/TD]
[TD]$85.00[/TD]
[TD]Call[/TD]
[/TR]
</tbody>[/TABLE]


What I want to do is a conditional formating the column of Strikes, that needs to met 2 criterias,
if the type is a "call" then if is below the stock price should be highlighted,
and if a put is resulting for the type then what's above the stock price should be highlighting

Thank you
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
Highlight the Strike values (in your example, that will be C2:C3). Select Conditional Formatting, New Rule, use a formula, =AND(D2="Call",C2 < B2) format how you'd like.<b2) format="" how="" you'd="" like.<="" html=""></b2)>
 
Last edited:
Upvote 0
This will probably work nice for the Call types, will this work for the puts type their is greater then the stock price?
 
Upvote 0
Highlight the Strike column, which I assume is C and use this formula:
=XOR($D1="Put",$C1<$B1)

It should work for both calls and puts, if those are the only 2 values in column D.

63falcondude, your formula with the AND will never highlight a Put. You could add a second rule:
=AND(D2="Put",C2 > B2)

but the XOR handles both cases.
 
Upvote 0
Eric W said:
Highlight the Strike column, which I assume is C and use this formula:
=XOR($D1="Put",$C1<$B1)

It should work for both calls and puts, if those are the only 2 values in column D.

63falcondude, your formula with the AND will never highlight a Put. You could add a second rule:
=AND(D2="Put",C2 > B2)

but the XOR handles both cases.
Click to expand...

Thank you! this is exactly what I want and works for puts and calls, (cell $B$1 i changed from being $B1, for my purpose of use)

to make it even better I would like to know if you can do this with a lookup function (and still use it in the conditional formating) =XOR($D1="Put",$C1<$B$1) instead of just formulating $C1<$B1 look it up the symbol and find the price, the lookup value is not all the time the same cell, but its will always be the first value that find in column A from the row we are staying to the way up, let say we're in cell C7 the value to lookup will be in cell A7 and if nothing there then the next place is A6 all the way up

[TABLE="class: grid, width: 300, align: center"]
<tbody>[TR]
[TD]AAPL[/TD]
[TD]95.00
[/TD]
[/TR]
[TR]
[TD]GOOG[/TD]
[TD]77.00[/TD]
[/TR]
[TR]
[TD]AMZN[/TD]
[TD]66.00[/TD]
[/TR]
[TR]
[TD]SPY[/TD]
[TD]55.00[/TD]
[/TR]
[TR]
[TD]QQQ[/TD]
[TD]44.00[/TD]
[/TR]
[TR]
[TD]DIA[/TD]
[TD]100.00[/TD]
[/TR]
[TR]
[TD]GLD[/TD]
[TD]23.00[/TD]
[/TR]
[TR]
[TD]USO[/TD]
[TD]11.00[/TD]
[/TR]
[TR]
[TD]TLT[/TD]
[TD]144.00[/TD]
[/TR]
[TR]
[TD]MSFT[/TD]
[TD]32.00[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
I'm not entirely sure what your table looks like.

If it looks like this:

ABCD
GOOGPut
Put
Put
Call
AMZNCall
Call
Call

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Symbol[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Price[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Strike[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Type[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]AAPL[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$95.00[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$85.00[/TD]
[TD="bgcolor: #FAFAFA"]Put[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FF0000, align: right"]$85.00[/TD]
[TD="bgcolor: #FAFAFA"]Call[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]77[/TD]
[TD="align: right"]55[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]44[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FF0000, align: right"]88[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]99[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]66[/TD]
[TD="bgcolor: #FF0000, align: right"]60[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FF0000, align: right"]62[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]70[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​



you can select range C2:C999 (or whatever), and use this formula:
=XOR(D2="Put",C2< LOOKUP(2^99,B$2:B2))*(C2<>"")
 
Upvote 0
Eric W said:
I'm not entirely sure what your table looks like.

If it looks like this:

ABCD
GOOGPut
Put
Put
Call
AMZNCall
Call
Call

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Symbol[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Price[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Strike[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Type[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]AAPL[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$95.00[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$85.00[/TD]
[TD="bgcolor: #FAFAFA"]Put[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FF0000, align: right"]$85.00[/TD]
[TD="bgcolor: #FAFAFA"]Call[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]77[/TD]
[TD="align: right"]55[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]44[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FF0000, align: right"]88[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]99[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]66[/TD]
[TD="bgcolor: #FF0000, align: right"]60[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FF0000, align: right"]62[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]70[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​



you can select range C2:C999 (or whatever), and use this formula:
=XOR(D2="Put",C2< LOOKUP(2^99,B$2:B2))*(C2<>"")
Click to expand...
Thanks Eric!
really amazing how this workout, I have no idea how this works, it's very interesting. Cool.
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Question Question
Frustrating intermittent VBA error dialog telling me that "User-define Type Not Defined"
Replies
1
Views
232
Micron
M
O
  • Question Question
Cannot trap error when using Excel's built-in stock price functionality in a macro. It used to work well!
Replies
1
Views
220
OaklandJim
O
D
  • Question Question
Conditional formatting for specific number of cells
Replies
9
Views
349
Fluff
Fluff
D
  • Question Question
If formula with date & VLOOKUP
Replies
1
Views
254
etaf
etaf
K
  • Solved
Using VBA to select a color coded cell
2
Replies
11
Views
900
Alex Blakenburg
A

Forum statistics

Threads
1,223,238
Messages
6,170,939
Members
452,368
Latest member
jayp2104

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top