Conditional formating

[Scuby]

Dear all,

i am sitting infront of a very nice problem. And I hope it could be solved via conditional formatting.

Untitled.png

As you can see in my matrix there are only two status... Open and Closed. Now I want to fill the cells
when the status is changed from open to closed (not in the row! Only in column) and the other way round.
In this cases the two cells where the status changed has to be filled in with a different color.

At the moment I have no idea how to realize this and I hope some of you could help me with this probolem.
Before asking you I tried to find something in google but without result.

Regards
Scuby

[etaf]

can you load a sample spreadsheet please - cannot really see the picture

also when you say change - do you mean if some types in open or closed - you want a colour

other wise you could use standard conditional formatting and change the fill colour based on if "open" or "closed"

as i cant see the image clearly - then i'm not sure what to suggest

but you can have the whole column change colour if any row contains the word "open"

I suspect you want to colour if any row has the word in all the rows "changed"
in which case that would need VBA to see the cell change

[Kaper]

Let's assume the first cell where statuses are written in is B3. Mark whole B3:L8 or whatever area where you have status one under another (empty line needs separate treatment)
And use the formatting rule based on formula
a formula like:

Please Login or Register  to view this content.

and one formatting
and for the same region next rule based on formula

Please Login or Register  to view this content.

and other formatting.

Play with that and next time post rather sample spreadsheet than a picture - see rationale behing in: http://www.excelforum.com/forum-rule...rum-rules.html

[Scuby]

Hi Kaper,

now attached find the spreadsheet. What I want to do is in a colum to mark the cells when a status changed. Both cells has to be marked.
Your idea was quite good but only depends on my bith cells and not indvidual on all cells.

Example:

open
open (marked)
close (marked)
close
close (marked)
open (marked)
close (marked)
close (marked)
open (marked)

I hope this will clarify the problem a bit.

Thanks for you assistance.

[Scuby]

Are there some ideas to solve this problem?

[Kaper]

Hi,
Have a look in the attached. Sheet1 has exactly the same formatting as proposed above.
The only change: addresses - because layout is different

Sheet2 has one combined rule, built slightly different way - including checking if any of involved cells (our formatted cell and one above ano one below are not empty.

[Scuby]

Hi Kaper,

thanks a lot this is exactly what I am looking for. Thanks for you great assistance.

[6StringJazzer]

Scuby, if your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

If a member helped you solve your problem, consider adding to their reputation by clicking on the star icon below their name.