Conditional Formatting - color groups based on row value

[djangeroo]

Hi,
I am new to the forum and impressed by the great advise that's out there.

This is my problem:
A list with numbers in column A, these numbers can be used over multiple rows, sometimes 3, 5 or more/less.
Every time this number changes, the color for the entire row should flip between yellow and no color.
Sequence of the number in column A is not always odd / even. I tried various suggestions regarding this topic but I do not get it straight.

Thanks for your help.

 

[GlennUK]

Hi NBVC,

Inserting a name in the middle of a group doesn't work ... the logic is based entirely on contiguous groups as you get with sorted data. If you add records to a column in the correct order they will highlight correctly ... if not, then you will have to sort afterwards. Glad it's not a big deal .... I hate to think of what a formula to do that automatically would look like.

 

[Peter_SSs]

I have been trying GlenUk's solution with some strange results. What am I missing? I entered A in cells A1:A3, B in A4:A5, C in A6:A8, D in A9:A11, E in A12:A13, F in A14:A100.

I then selected A1:A100 and in Conditional Formmatting... applied the formula =MOD(ROUNDUP(SUM(1/COUNTIF($A$1:$A1,$A$1:$A1)),0),2)=1 and chose my colour.

The first 34 rows seem to work fine, but here is a sample of the results after that:

Mr Excel.xls
	A	B	C	D
33	F
34	F
35	F
36	F
37	F
38	F
39	F
40	F
41	F
42	F
Cond Format In Groups (2)

..and near the bottom of my range I got this:

Mr Excel.xls
	A	B	C	D
87	F
88	F
89	F
90	F
91	F
92	F
93	F
94	F
95	F
96	F
97	F
98	F
Cond Format In Groups (2)

Anybody else get this or have an explanation?

I also tried applying the Conditional Format to about 30,000 rows and Excel just hung.

 

[GlennUK]

Change the ROUNDUP to ROUND. I was assuming that rounding errors in Excel were always going to make the totals short by a fraction, but obviously they go over the count by a fraction too.

 

[Peter_SSs]

Change the ROUNDUP to ROUND. I was assuming that rounding errors in Excel were always going to make the totals short by a fraction, but obviously they go over the count by a fraction too.

Glenn, this certainly fixed that problem.

However, even with 1,000 rows when I used your formula, I found that sorting the column, scrolling up/down, using CTRL+HOME etc to move around all seemed to take a LONG time, whereas the formula I suggested on page 2 of this thread seems to work much faster by comparison. Have you tested say 1000 rows? And do you get the same speed issues?

 

[GlennUK]

I wouldn't even bother testing it on a 1000 rows ... this uses array formual calculation, which gets exponentially slower the more rows your conditional formatting covers ... it'll be fine over a couple of hundred rows, but for large areas I'd use a helper column.

 

[CW Moss]

Hello,

I am new to this forum and found it by search for a solution to my issue. It is similar to the original posters with the exception that I would like to conditionally format a row of data based on the Duration Hours column B as follows;

If B is less than 12 then then row is remains white
If B is between 12and 24 then the row is highlighted yellow
If B is greater than 24 then the row is highlighted red

I though Erik's solution to the original poster may have worked with some modification but after reading through the entire thread I have to admit that I am lost for my expertise is limited

Can you guys help or should I start a new thread?

I tried to past in an example below but only the text displyed.

A B
Process Duration Hours
1 AT31 187.7 Row Highlighted Red

2 AT32 261.2 Row Highlighted Red

3 AT33 4

4 AT34 493.53 Row Highlighted Red

5 AT35 11.99

6 AT36 18.2 Row Highlighted Yellow

7 AT37 640.7 Row Highlighted Red

8 AT47 33.7 Row Highlighted Red

9 AT48 3.7

10 AT49 1594.7 Row Highlighted Red

11 AT50 12 RowHighlighted Yellow

12 AT51 23.7 Row Highlighted Red

Thanks for your help in advanced.

 

[Peter_SSs]

CW Moss

Welcome to the Mr Excel board!

Firstly, to show a sample of your sheet, you need to see the link at the bottom of this page to Colo's HTML Maker. Also read the ‘Sticky’ titled: 'HTML Maker FAQ (How to show your sheet on the board)' which is at the top of this ‘Excel Questions’ forum. Test it out in the ‘Test Here’ forum which is found in the ‘MrExcel Message Board Forum Index’ before trying to use it in this forum.

Now to your question. To achieve what I think is the result you want (shown below), I selected A2:C13. Then Format|Conditional Formatting...|Formula is: =$B2>24 Click 'Format', choose red and click OK.

Now click Add>> near the bottom of the Conditional Formatting window and for Condition 2 use Formula is: =$B2>=12 Click 'Format', choose yellow and click OK, OK.

You will note that the last row says 'Row highlighted red' but the Duration hours were less than 24 so it has actually been highlighted yellow.

Mr Excel.xls
	A	B	C	D
1	Process	Duration Hours
2	AT31	187.7	Row Highlighted Red
3	AT32	261.2	Row Highlighted Red
4	AT33	4
5	AT34	493.53	Row Highlighted Red
6	AT35	11.99
7	AT36	18.2	Row Highlighted Yellow
8	AT37	640.7	Row Highlighted Red
9	AT47	33.7	Row Highlighted Red
10	AT48	3.7
11	AT49	1594.7	Row Highlighted Red
12	AT50	12	Row Highlighted Yellow
13	AT51	23.7	Row Highlighted Red??
14
Highlight on Duration Hours

 

[CW Moss]

Thanks so much Peter, your solution would work but is the anyway to automate this with code so that as data is added the spreadsheet or the spreadsheet is refreshed with new data the new row or rows will reflect the condition set in its respective cell in column B?

 

[Peter_SSs]

Yes, I am sure this could be automated with code, but I am not the best person to tell you how.

However, we need to confirm that code is required. How is the data being added/refreshed? Is it manual entry or is data somehow being copied into the sheet?

If the entry is manual, then the conditional formatting could be applied to a larger range first (say A2:C100) and then the rows will remain white until data is entered and then colour according to the conditional formatting.

If you need more assistance, post back and I am sure somebody will help.

 

[MrDoc]

Hi,

I tried NBVC's solution with Erik's formula shortening. The column where values (dates written as dates) change is column C. I'd like to apply the highlight to columns A to F.
I tried to apply conditional formatting, by using the helper column, to the whole range (A2:F2000), but it did not work.
I applied it only to column A and all went well. Then I tried to apply it to column B and it did't work... I'm confused. Can anyone please help?