# Conditional format formula

#### Brew

##### Well-known Member
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

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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)

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

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.

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)

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.

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.

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

Hi

Isn't it simply

Select
DA22:DA106

CF formula =

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

?

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

Replies
5
Views
716
Replies
0
Views
321
Replies
1
Views
107
Replies
1
Views
226
Replies
2
Views
221

1,221,202
Messages
6,158,504
Members
451,497
Latest member
something68

### 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.

### Which adblocker are you using?

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

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