Products > Programming

Excel Question

(1/5) > >>

bostonman:
I'm creating a spreadsheet and would like to color every other number of rows (more than one).

Unfortunately it gets a bit more complicated. A particular item may have six rows of data associated with it while another item may have seven.

Can I check whether B3 contains text, and, if so, color that row plus the next X rows until Excel sees text in the same column (say B8)?

Here is an example:

<A1> <part number> <data>
                                 <data>
                                 <data>
                                 <data>

<A5> <part number> <data>
                                 <data>
                                 <data>

So I'd want the four rows for A1 to stay white, and the three rows for A5 to be a different color. Then the next time something shows in column B, it makes it white.

ledtester:
Create a new hidden column which will contain either a 0 or a 1.

The formula for that column is:

If the part number field is populated, value = 1 - the value in the previous row.
Otherwise the value = the same value in the previous row.

You'll have to add a new row at the top to specify an initial value for this column (either 0 or 1).

This will group together rows which belong together and alternate the value among the groups.

Now you can color the rows based on this column.

bostonman:
How would it automatically color an entire row?

Below is an example of the locations data would be in. I'm using the actual Excel locations to represent data in those cells. The actual data is in many more rows, so data would be from A1 to H1. So in this example, I'd need to keep A1 to A4 white but across to H1 to H4, A5 to A6 blue but also across to H5 to H6, next set of data (which may be less or more rows than previous) white, etc...

This will be over 100s of rows and across to around column H.

<A1> <B1> <C1>
         <B2> <C2>
         <B3> <C3>
         <B4> <C4>
<A5> <B5> <C5>
         <B6> <C6>

ledtester:
Once you have the new column set up just follow the instructions in this answer:

https://superuser.com/questions/625597/changing-a-rows-color-depending-on-the-value-text-of-a-cell

Another article on conditional formatting:

https://www.howtogeek.com/howto/45670/how-to-highlight-a-row-in-excel-using-conditional-formatting/

bostonman:
Adding a column to use as 0s and 1s is a great idea.

I'm having trouble trying to create a formula that would flip the 0s and 1s though.

Currently I have data in B3 and I need from B3 until B8 to remain white. At B9 is a new piece of data, and I need from B9 to B14 to be shaded (which I'll use conditional formatting for), and then at B15 to etc... to be white, back to shaded, etc...

So I need a statement that will keep the column at a '1' from a cell that has data, the following empty cells, and then flip to '0' when it sees a cell with another piece of data.

Navigation

[0] Message Index

[#] Next page