Conditional format formula

Brew

Well-known Member
Joined
Sep 29, 2003
Messages
1,569
In range da20:da106, I have records in each cell with either -450 or 900 value. How do I create a conditional format formula to identify each cell within this range that has a record of 900, which immediately follows 2 or more consecutive -450 records in the range?

Example
Da20=900
Da21=-450
Da22=-450
Da23=-450
Da24=900
Da25=-450
Da26=-450
Da27=900
Da28=900
In this example, only da24 and da27 meet the condition
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Hi Brew, where've you been? :)

If you select your range (DA20:DA106) with DA20 active cell then apply this formula within conditional formatting

=(DA20=900)*(SUM(DA18:DA20)=0)
 
Upvote 0
Yep, that is it... Thanks Barry.
Hey, it's good to be missed.
I had 2 take a break from my creative developments in this area.

dK
 
Upvote 0
When I changed the requirement to where the record can be either -465 or 900, rather than -450 or 900, the conditional format formula does not execute when the condition is a match.
 
Upvote 0
Hi Brew:

Select the range starting from cell DA20 down, and use the following Conditional Formatting formula ...

=AND(DA20=900,COUNTIF(DA18:DA19,-450)=2)
 
Upvote 0
I forgot to mention a change, DA20=4,600, however every other value in the range is either 900 or -465
so, I followed your instruction with the range, starting with DA21 instead of DA20, with the following modification to the formula:
=AND(DA21=900,COUNTIF(DA19:DA20,-465)=2)
I thought it should have worked. It is 2nd condition in each cell's format. However, it still does not execute anymore. The 1st condition is if cell value is equal to -465, which works fine.
 
Upvote 0
Hi Brew:

How about restating the complete problem in one setting without reference to other posts so that we can get a clear and unamiguous interpretation in one go ... and take it from there.
 
Upvote 0
Restating the problem
In range da21:da106, I have records in each cell with either -465 or 900 value. How do I create a conditional format formula to identify each cell within this range that has a record of 900, which immediately follows 2 or more consecutive -465 records in the range?

Example
Da20=4,600
Da21=-465
Da22=-465
Da23=-465
Da24=900
Da25=-465
Da26=-465
Da27=900
Da28=900
In this example, only da24 and da27 meet the condition
 
Upvote 0
Hi

Isn't it simply

Select
DA22:DA106

CF formula =

=And(DA22=900,DA21=-465,DA220=-465)

?
 
Upvote 0
I think there is an error with the formula
=And(DA22=900,DA21=-465,DA220=-465)
there should not be a DA220 reference in the formula
so should the formula be
=And(DA22=900,DA20=-465,DA21=-465) ?
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

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