For conditional formatting, how to compare each number in a row with the first cell in that row?

Rnkhch

Well-known Member
Joined
Apr 28, 2018
Messages
578
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have several rows of numbers, and in each row, I need to check whether numbers after column A are at least 1 less than A, and if they are, I want them to be highlighted. I have attached an example XL2BB with manual highlighting of the relevant cells.

I'm having a hard time with the formula. I wrote this formula, but it highlights entire rows not the particular cells that satisfy the condition:

=($B1:$J1)<=($A1-1)

Also can this be done with just one formula for all the rows? Or each row would need its own formula?

Thanks for any input!


Book1
ABCDEFGHIJK
13736968787
28736444634
37786456635
49444835643
56735985785
66397686499
7
Sheet1
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
You can use the inbuilt "format cells that contain" for this

1658011310853.png
 
Upvote 0
Solution

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

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