VBA to list rows with coloured/conditionally formatted cells

Hi all,

I have conditional formatting set up on a set of data (columns A-D, ~1000 rows) to colour certain cells in red. I need a VBA script (that will be incorporated into a macro) that will list the values in column 'A' of any row with a red cell in it at the top of column F. Some rows could have two red cells, but need to only be listed once. Additionally, if there are no red cells, it should show 'None'.

I have a variation of this working already using data validation and 'circle invalid data', but I'm keen for it to work with conditional formatting if possible.

Is anyone able to help me? I've attached an example of what I'm looking for.

Thanks,
Jamie

Please Login or Register  to view this content.

Kind regards
Leo

Hi Leo,

Thanks for your reply. That does work very well, but what I meant by the 'None' thing, is if there are no red cells at all in the range it says 'None' in cell F2. If there are red cells, only those ones would be listed. Do you think that's possible?

Thanks,
Jamie

Think of this

Please Login or Register  to view this content.

Kind regards
Leo

Correction

Please Login or Register  to view this content.

Cheers
Leo

Hi Leo,

Thanks, that's great. The only thing that I've noticed (my fault, didn't explain very well!) is that your script specifically looks for 'A', 'D' or 'F'. The values I used were just examples. In reality it will (hopefully) be able to find anything in a red cell, but the value in the red cell could be a lot of different things. Is there anyway of specifying by colour instead of value?

Thanks,
Jamie

Try

Please Login or Register  to view this content.

Hi jindon,

That seems to be bugging at 'If Evaluate(r.Address & fc.Formula1) Then'.

Thanks,
Jamie

No error for me

PHP Code: 

your script specifically looks for 'A', 'D' or 'F'. 


same condition as in conditional formatting, not ?

you can change this part

Please Login or Register  to view this content.

to other conditions

Cheers
Leo

Originally Posted by jamiem4

Hi jindon,

That seems to be bugging at 'If Evaluate(r.Address & fc.Formula1) Then'.

Thanks,
Jamie

If you are running on different workbook with different condition(s) then

Please Login or Register  to view this content.

Hi Leo,

How would I change it to match the conditional formatting formulae? All of my conditional formatting is in the formula format, but I can't work out how to integrate that into your script.

Thanks,
Jamie

Originally Posted by LeoTaxi

PHP Code: 

your script specifically looks for 'A', 'D' or 'F'. 


same condition as in conditional formatting, not ?

you can change this part

Please Login or Register  to view this content.

to other conditions

Cheers
Leo

Use the same conditions in the if statement as in the conditional formatting

if you have a file, i will do it for you


Kind regards
Leo

maybe if i have the file with the working script

Unfortunately I can't send the file due to the sensitivity of the data, and some of the other conditional formatting formulae used are also sensitive

it dont have to be THE file
you can create 1

cheers
Leo

Please find an example file attached.

The aim is to have the corresponding values from column C of red cells in the 'Format Check' columns listed from cell I5 downwards.

Hope that makes sense!

Thanks,
Jamie

This is a file where the script dont work

i ame asking for a file with working script ( youre own script for conditional formatting)
so the file i ame asking contains a few red marked cells


cheers
Leo