Excel help, can it even do this?

Moonpie2 · 8 Oct 2018 at 10:36

Hi all

I tried to find the answer to this to no avail.
Does anyone know if this is possible?

Basically, I want to put an X in the cell in column C if the cell in column I is highlighted green and then just copy paste all the way down column C.
Sounds simple?

Any help?

Lartyconshayo · 8 Oct 2018 at 12:09

Possibly condtional formatting in reverse ?

Moonpie2 · 8 Oct 2018 at 12:11

Unless you have any idea how to do that, then I know I don't.

Lartyconshayo · 8 Oct 2018 at 12:13

https://stackoverflow.com/questions...ditional-formula-based-on-the-color-of-a-cell

Lartyconshayo · 8 Oct 2018 at 12:13

I'm too rusty to do it but seems to be the ballpark alright. That stack overflow link seems pretty same question as yours with an answer

skyripper · 8 Oct 2018 at 12:14

If this is a one-off, Filter by colour, then add a column with a value per colour.
Your formula can use that value as part of the IF statement.

Moonpie2 · 8 Oct 2018 at 12:20

That stackoverlow looks promising.
A little convoluted, but might save time down the line.

I'll give it a whirl later. Also potentially the skyripper suggestion too !!

Moonpie2 · 8 Oct 2018 at 13:16

Solved quite easily.
Here's the solution.

I was using conditional formatting to highlight the duplicates in column I using..... =COUNTIF(I:I,I1)>1

So then instead of doing another formula to detect the highlighting, someone suggested to forget the conditional formatting in column I and just go straight for column C with.... =IF(COUNTIF(I:I,I1)>1,"X","")

Did the trick nicely

Only problem is that once the duplicates are removed, the Xs also go, so I need to copy and 'paste special' them. No biggie !!

skyripper · 8 Oct 2018 at 13:33

You do know Excel has a Remove Duplicates function?

Moonpie2 · 8 Oct 2018 at 13:37

Yes, but I don't want them removing.
Well.... not straight away.....

It's hard to explain

Jamauk · 21 Oct 2018 at 08:45

I used conditional formatting to do this the other way around last week, whereby if cell A1 = "Saturday" then colour grey. I can't remember the code off hand. So as said above, conditional formatting in reverse.