Excel Conditional Formatting a series urgent help please

**inmyblood** · 02-24-2014, 03:57 PM

Hello all,

I hope someone can help me with this question, its really important for me to advance my career right now.

Can I conditional format a series in excel where if there is a break in the series the cells before and after the break will be highlighted ?

For example if I have a column with 1.2.3.4.5.6.7.8.9.12.13.14.15.16 going down. I need to conditional format the whole column where cells containing 9 and 12 are highlighted in red or any color. .

I believe this should be possible thanks !!

**etaf** · 02-24-2014, 04:07 PM

what will the series be - will it change - or is simply as shown

assuming starting at A2

=IF(OR(A3="",A1=""),FALSE,IF(AND(A2-1=A1,A2+1=A3),FALSE,TRUE))

IF its a different series, then you need to test the expected result

see attached

**inmyblood** · 02-24-2014, 04:14 PM

That's Beautiful work! yes I believe that is exactly what I need.

Just 2 questions: what do you mean exactly that the series will change ?
Also since I am a beginner with this conditional formatting could you guide me as to where do I insert this code. lets say my series starts at cell BH207 and ends at BH220. Do I select this column and press on conditional formatting and insert the code you wrote ?

Thank you very much !

**etaf** · 02-24-2014, 04:38 PM

what do you mean exactly that the series will change ?

well a series could be anything like

2,4,6,8 just adding 2
2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37 - prime numbers
1,3,5,7 odd numbers

Also since I am a beginner with this conditional formatting could you guide me as to where do I insert this code. lets say my series starts at cell BH207 and ends at BH220.

for 2007 or 2010 excel version
Conditional Formatting

Highlight applicable range >>
BH207:BH220

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:

=IF(OR(BH208="",BH206=""),FALSE,IF(AND(BH207-1=BH206,BH207+1=BH208),FALSE,TRUE))

Format… [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

**inmyblood** · 02-24-2014, 05:12 PM

Thank you for the clarification,

Yes the series is just as shown.

The problem I am facing now is that when I want to apply the formatting to a different cell range I am getting confused as to what I need to edit in the code. For example if now the cell range under which this rule must apply is BH8 to BH 25 I would have to select that range and edit the original code from :

=IF(OR(BH208="",BH206=""),FALSE,IF(AND(BH207-1=BH206,BH207+1=BH208),FALSE,TRUE)).

To

=IF(OR(BH9="",BH7=""),FALSE,IF(AND(BH8-1=BH7,B8+1=BH9),FALSE,TRUE))

Its not working :/

Maybe there is a way I don't have to edit the code every time I need to change the range ? like a custom code for selected range ?

Thanks!

**etaf** · 02-24-2014, 06:37 PM

should work
can we see the spreadsheet
make sure you have the correct range applied to in the conditional formatting

**inmyblood** · 02-25-2014, 06:46 PM

Thanks actually I just tried it again it seems that the problem was that the first cell in which this code must work on had a header/or was the beginning of new information series other than in the cell above it and I guess that's what messed it up. to fix it I decided to start the rule on the second cell in the beginning of the series. so if the sheet looks like 99.1.2.3.4.5 I would start with rule the cell containing 2..

I have one final question is there a way to ask excel to fill in a series however if the cell next to it is highlighted to ignore and fill series only when the cell next to it is not orange ?

like if a column was going 1.2.3.4.5.6.7.8 downwards and the cell bellow 8 had a cell next to it which was highlighted orange to ask excel to skip that one and fill in 9 in the next one below it with no highlighted orange in the column next to it?

Hope that made sense. thanks again

**etaf** · 02-28-2014, 04:50 AM

can you explain again please

you have
1.2.3.4.5.6.7.8.9.12.13.14.15.16
and 9 and 12 are highlighted

now for you new question , what do you want to happen for
1.2.3.4.5.6.7.8.9.12.13.14.15.16

Excel Conditional Formatting a series urgent help please

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Excel Conditional Formatting a series urgent help please

Re: Excel Conditional Formatting a series urgent help please

Re: Excel Conditional Formatting a series urgent help please

Re: Excel Conditional Formatting a series urgent help please

Re: Excel Conditional Formatting a series urgent help please

Re: Excel Conditional Formatting a series urgent help please

Re: Excel Conditional Formatting a series urgent help please

Re: Excel Conditional Formatting a series urgent help please

Thread Information

Users Browsing this Thread

Similar Threads

Urgent help with conditional formatting

Applying conditional formatting to a multiple series chart

Conditional Formatting a data series in a graph.

Conditional Formatting of Data Series in a chart

[SOLVED] RE: Conditional formatting of a data series in an Excel Chart?

Bookmarks

Bookmarks

Posting Permissions