Forum Discussion
Excel conditional formatting
I have a column that is time and a column with item x I cannot have two of the same item in a two hour period. I have used conditional formatting to highlight for a two hour block from 1-3,4-6,etc. works great except it lets me put a 3 and 4 of the same item x in the column. I tried adding another conditional formatting but then it starts highlighting everything. Is there any other way to format this to highlight a duplicate just within a six cell block of itself?
Time Item X
1 g not ok OK
1 c I I
2 f I I
2 I I
3 g Not ok I Not ok
3 I I
4 g OK Not ok
4
5
5
12 Replies
You may use conditional formatting rule with formula like
=COUNTIFS( itemX, currentValue, time, "<=" & thisTime + 1 hour time, ">=" & thisTime - 1 hour )I am trying to teach myself Excel and have been at it a couple days this is by far the hardest thing I am trying to do....
Will this work if item x is any value at any time? Essentially If item x is a at 3 and 4 both need to be highlighted but not at 3 and 5 but if item x is b at 3 and 4 both b's need to be highlighted but not at 3 and 5, or if item x is c at 10 and 11 both c's to be highlighted but not at 1 and 10. Sorry if this is confusing it is for me. I know what I want it to do I am just trying to figure out how to get it to do it. If I need a condition for each Item X at each time that will not be fun.
