Search column for cell with specific formatting.

[JohnZ1156]

I have a column with consecutive numbers. I used “Conditional Formatting” to format a cell if there is a number that is not consecutive in the column.

This is my Conditional Formatting rule description: =AND(A18<>A17+1, A19<>A18+1).
It changes the format to fill “Red, Accent 2, Lighter 40%”.
Right now, I have to scan down 7000+ rows to check to see if there is a number in a cell that is not consecutive by looking for the format changed cell..

Question:
Is there a formula that I can place in a cell outside this column that will tell me if a cell within the column has this “Red, Accent 2, Lighter 40%” format,
and better yet, which row the non-consecutive cell is in?
A formula that constantly looks in the column would be better than VBA, but I can use all the help I can get.

Thank you.

 

[etaf]

in a new column put the formula that you have used for conditional formatting
=AND(A18<>A17+1, A19<>A18+1)

copy down the list - if in a column next to the data - double click on the black cross bottom right of cell and it will autofill

then you will get TRUE / FALSE

then use filter on TRUE - then you will see all the conditional formatted cells

 

[JohnZ1156]

Thank you,
I thought of that. Requires inserting a column, entering formula, copying down, using Filter.
I would rather not have to go through all those steps, you see and I really don't want the inserted column permanently in my table.

 

[etaf]

oh ok

Is there a formula that I can place in a cell outside this column

i thought you wanted a formula in a new column

and better yet, which row the non-consecutive cell is in?

will there only be 1 ? ever out of the 7000 that is wrong - or multiple cells

A formula that constantly looks in the column would be better than VBA, b

i think i need to see examples of what you want

ALSO what version of excel are you using - the profile says 2010

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

 

[JohnZ1156]

Sorry, I'll update my profile.
MS Excel 2021 (Office Pro 2021)