Difficult conditional formatting

**amartino44** · 10-11-2013, 05:21 PM

Hello. I have the following line items in column A. I would like to create a conditional formatting that does the following. Based on value in A, color rows a certain color. If column A value starts with mb (NOT case sensitive) or has only six numbers at the beginning (for example, 601800 as apposed to 601800 601800 etc) I want to color it one color, and everything else (except for blanks) should be colored another color. Please help.

601190 601190 5007 ACCRUED PTO
601150 601150 5033 WAGES OVERTIME
601800 601800 5043 SEVERANCE

mb - other salary expense

602000 602100 5021 401K MATCH

mb - deferred compensation

601001 601200 5042 COMMISSIONS
601001 601300 5046 OTHER COMP - WELLNESS REWARDS
601001 601300 5049 OTHER COMPENSATION
601001 601200 5041 INCENTIVE COMPENSATION

621100 occupancy expenses

**FDibbins** · 10-11-2013, 05:23 PM

For your CF rule use (use formula) this...

=or(A="MB",len(A1)<6)

If you color everything using regular formatting, you wont need a 2nd rule

**amartino44** · 10-11-2013, 05:57 PM

I think that's close but no cigar. The "MB" part is fine but if there are two line items: 1) 601800 601800 5043 SEVERANCE and 2)
621100 occupancy expenses .... I want to reference the second type in the conditional formula. It is NOT less than 6 characters, it just has 6 numbers to start as apposed to the 16 number in the other line item.

Also, you forgot to fix the A column with "$A" otherwise it doesn't work. And I also need to include something so it doesn't format where the rows are blank. Please help.

**FDibbins** · 10-11-2013, 06:13 PM

Well that gives you a start, hope you get it resolved

**amartino44** · 10-11-2013, 06:17 PM

Actually, it makes no sense because it's not that it = MB, it's that it starts with MB. Excel experts can't read.

**FDibbins** · 10-11-2013, 06:31 PM

You may have read that my title is Moderator, not expert?

Be that as it may, a wise man once said...if you have noting good to say - say nothing

**newdoverman** · 10-12-2013, 04:23 PM

For the MB part select the column, Conditional Formatting, create New Rule, use formula... and enter

Formula:

Please Login or Register  to view this content.

and select the formatting of your choice

The other requirements are something else.

**popipipo** · 10-13-2013, 05:12 AM

=NOT(ISNUMBER(MID(A1,8,1)*1))
Try this one

**daddylonglegs** · 10-13-2013, 05:44 AM

Originally Posted by amartino44

....Excel experts can't read.....

I read that part OK

With an attitude like that you may find that contributors here are disinclined to answer your queries

Difficult conditional formatting

LinkBack

Thread Tools

Rate This Thread

Display