define a name to get the fill color of cells.

add one more conditiongal formatting rule, priority set the first and check the check box.

when the first rule is satisfied, it will stop other rules.

may be my understanding on the function was not right, but please try to see if it is what you want.

can this solve your problem?

Please see the second attachment, i cant remove the first one.

Thanks for this one Clark! I tried it out. It does work, but not everytime. Sometimes you don't see the color (specially in the uneven rows where already the 'bottom' color is). When you then go to conditional formating and open your condition, close it and click ok, it works also on these cells.

So it's a nice workaround. Because of the macro not completely what I'm looking for, but nice to see that it can be done. 

I hope there is another (more easy) way, but this one is good near at hand.

Frans

please try this one in attachment in this post.

1)select cell,  manually color the cell

2)the conditional formatting works as you wish

3)if the conditional formatting works not properly, as you mentioned it only works to cells where already the 'bottom' color is, press F9 on the keyboard.

is this easy enough:)

I made an amendment to the defined name, color=get.cells()&t(now()),this will refresh automatically.

Hello Frans,

Now I don't know if you intend to have some text in those cells which you are colouring, but if not then a simple and macro free example is to create a third condition. As the information in this Microsoft help page shows, you need to have this new condition before your odd row-colouring condition, otherwise it will not show. See attached file for an example.

In this new condition I have set it to check for two criterias --> =AND((F$2="a");(F7=1))
So, row 2 with start from column F must contain the letter a and then the cell which you want to colour must contain the number 1. If those two criterias are true, then the cell will be formatted.

In this example I have put the font colour to be the same as the background fill colour, so the number 1 is not showing.

Br,
Anders Sehlstedt

Anders, you put me on the right track! It's easier than you're solution.

I only have to color the text the same color as I want to format the cells when I want to 'mark' them by hand.

And to mark them I use for instance the '1' as you suggested.

Only conditional formatting rule (on top of the others indeed) is: =F7=1 and then color the cell the same color as the text.

That I didn't thought of this myself at once?

Thanks! This is nicer (sorry Clark) than the macro solution.

So thank to everybody here!

Hello Frans,

Great that you found a working solution. Have an Excellent day now!

Br,

Anders Sehlstedt