Grey bar

[BigD63]

Hello all,
this one might be a bit odd, but when in large excel files particularly when dealing with lots of columns, I find it easier to keep my place if I make the sheet basically greybar.

Now, I do it manually by selecting every even numbered row, and selecting "Fill Color", basically highlighting. This is fine as long as I don't need to sort, as the fill is attached to the data not the cell.

Is there a way to make the fill attached to the cell number not the data so if i sort the greybar stays where I want it. Is this possible?

[Greg M]

Hi there,

The attached workbook uses the following formula to conditionally format the even-numbered rows from 1 to 100:

Please Login or Register  to view this content.

Hope this helps - please let me know how you get on.

Regards,

Greg M

[BigD63]

WOW, thanks Greg.

This maybe an eyeroll worthy question, but where do i put in that equation?
Also, if I need to go beyond 100 how would I adjust that?
In that equation, how was grey picked?

This looks like the perfect solution, just don't understand how to reproduce it?

[Greg M]

Hi again,

Many thanks for your feedback.

Click on HOME >> Conditional Formatting >> Manage Rules

Select the Rule (there's only one!) and click on Edit Rule

You'll see where the formula is inserted, where the formatting colour is selected, and also the range to which the conditional formatting applies.

Hope this helps.

Regards,

Greg M

[Pepe Le Mokko]

You can also select your range - Insert Table - Check or don't check " My table has headers" -In the Table Tools - Design, select your design
The row banding will adapt when you extend your range

[BigD63]

OHH, ok.
So i select the rows up front then do the conditional formatting, gotcha.

Replicated and worked as intended.

I have to say though, while it worked, i don't fully understand the actual info i put into the conditional formatting. Would you mind going over that as well?
=MOD ok, modifying
ROW as opposed to column (this is whats being modified)
() what is that representing? Since no data is being modified, is that why there is empty parenthesis?
, 2 i know i have used this before, but I honestly don't remember what this means
= 0 Not sure what this means?

Yeah, im full of questions.

Thanks again

[Pepe Le Mokko]

perhaps have a look at the syntax of the MOD() and ROW() functions

[Greg M]

Hi again,

First of all, many thanks for your very prompt feedback and also for the Reputation increase - much appreciated!

You're welcome - glad I was able to help.

As far as the formula is concerned:

Please Login or Register  to view this content.

ROW() returns the row number of the cell in which the formula is entered

MOD(a, b) returns the remainder when a is divided by b

= 0 is true whenever the row number is even (a remainder of zero occurs whenever an even row number is divided by two)

Hope this helps.


Best regards,

Greg M

[Pepe Le Mokko]

And with table formatting no formula is needed