Diffulties With Conditional Formatting

[Jambo11]

I want to use conditional formatting to apply shading to the cells to form a diagonal pattern, but I can't figure out how to get the pattern to go the other way.

Since it's probably easier to show it than explain it, I'll do just that:

I can get Pattern 1 just fine, but because of how the columns are numbered (and my own ineptitude), getting Pattern 2 is proving to be problematic.

I realize that the issue of cell colors forming patterns is trivial, at best, but I'd like to think that finding a solution to minor issues like these (even if all I "found" was, "New Thread" button), my skills with Excel will improve a little bit.

 

[njfeathe85]

Not really sure what you're trying to do, but reading what you've saide if the shading goes down one way, maybe try using a -ve number to see if that alters the direction?

 

[Jambo11]

A "-ve number?"

You'll have to elaborate.

 

[njfeathe85]

You've not really told us what your conditions are, in order for it to be formatted in such a way... -ve=negative

 

[njfeathe85]

I have just tried the -ve number in spreadsheet and it didn't work. Not really sure...What exactly does th MOD formula do? And how is the number in the forumla related to the overall formatting?

 

[Jambo11]

You've not really told us what your conditions are, in order for it to be formatted in such a way... -ve=negative

The formulas are in the picture I posted, and what they do is produce the pattern shown in the picture, on the left side.

As I mentioned, my aim is to, figure out the formula to make the diagonal pattern go the other way.

The formula on the left shades in the dark gray.

The formula on the right shades in the light gray. This formula affects both the dark gray and light gray cells that you see in the picture.

Since I didn't know how to offset the fill pattern that the formula "=MOD(COLUMN(),4)=MOD(ROW(),4)" produces, I just changed the "4" to a "2," making it fill in every 2 cells, instead of every 4. I put this formula below the other, so it wouldn't matter that I didn't offset the fill pattern; the extra cells that it fills in would be overridden by the other formula.

 

[njfeathe85]

Ah right, fair enough. Sorry then I can't help you...I have however, spotted that to reverse it the even columns (if you were to think of them as numbers as opposed to letters) would need to be changed the other way so you'd have dark grey every 2 cells and light grey every 4.

I have done that on my computer and it has worked, but obviously you have to manually select the "even" columns and apply conditional formatting to them as described above.

I don't know how to make it automatic if that makes sense.

 

[Jambo11]

Yeah, that's kinda what I was talking about when I said "because of how the columns are numbered." As I understand it, technically, the columns are numbered, but letters are shown letters for the sake of user-friendliness.

Any idea how the fill pattern can be offset? If I can't find a way to reverse the pattern, the least I can do is find a way to make the formula for the light gray affect only the cells that I want them to. If I can get that figured out, it might be possible to combine the formulas for the two colors into a single formatting rule; of course, not that it matters in the end, but it's just a goal that would result in me gaining greating understanding of Excel.

 

[Jonmo1]

Well, I've racked my brain...But found a solution.
Probably not the prettiest...

Your original formula
=MOD(COLUMN(),2)=MOD(ROW(),2)
Still works for the light grey cells.

Just need to reverse the dark grey cells
Which you used 4 originally in the mod...

So I just put this formula in the range A1:H8
=MOD(COLUMN(),4)+MOD(ROW(),4)
On the offhand chance it would display a pattern..
And it did.

The cells you want colored dark grey return a 1 or a 5.
None of the other cells returned 1 or 5 from that formula.

I then expanded it out to A1:Z50, and the pattern continued.


So, you can use this..

Condition 1
=CHOOSE(MOD(COLUMN(),4)+MOD(ROW(),4),1,0,0,0,1)
Format for DARK Grey

Condition 2
=MOD(ROW(),2)<>MOD(COLUMN(),2)
Format for LIGHT Grey


Hope that helps.

 

[Jambo11]

Awesome!

Thank you for the help!