# Help with conditional formatting (may be complicated)

#### Rashie

##### Board Regular
Hi All,

I could use some help with this particular challenge I'm facing.

Below is some sample data

 Thu Fri Sat Sun Mon Tue Wed Thu Market Departure Time CXR & FlightNumber Name 24 11 2016 25 11 2016 26 11 2016 27 11 2016 28 11 2016 29 11 2016 30 11 2016 01 12 2016 LONEGG 07:30 gina £7.00 £11.20 £7.50 £8.40 £7.00 £7.00 £84.00 £8.40 LONEGG 09:00 richard £125.00 £10.00 £12.00 £125.00 £6.00 £6.00 £6.00 £6.00 LONEGG 10:00 gina £24.70 £8.40 £9.20 £2.00 £11.20 £9.00 £84.00 £4.00 LONEGG 12:00 richard £125.00 £15.00 £12.00 £20.00 £10.00 £7.00 £7.00 £6.00 LONEGG 12:30 gina £24.70 £13.30 £9.00 £24.70 £11.20 £9.20 £84.00 £8.40 LONEGG 14:30 gina £24.70 £8.40 £7.00 £25.50 £9.20 £4.00 £75.00 £7.00 LONEGG 15:00 richard £15.00 £15.00 £10.00 £20.00 £10.00 £7.00 £7.00 £7.00

<tbody>
</tbody>

The report pulls through the information in this ungodly format. The main person I'm interested in is Gina.
Each line represents a cost for a bus route from LON-EGG and the respective ticket prices for each time for each date.

The information I'm interested in highlighting through conditional formatting would be any price that Gina has set that is less or more than a £2 difference than (this is where it gets complicated) for any ticket Richard has set only if there is an hour or less between flights.

So for this particular example, I would really only want to know about Gina's 10:00, 12:30 and 14:30 tickets, should they differ from any ticket within an hour of them that has a £2 or more difference.
The reason I wouldn't be interested in the 07:30 ticket is because the next ticket isn't until 09:00 which is 1:30 later.
Any of Gina's prices that differ by £2 or more from any flight within an hour of one of Richards should show up red.

Thanks for your help - there is an ENORMOUS amount of data I need to work through so to flag them up red would be doing me a massive favor but I am completely stumped!!

Thanks

Rashie.

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

#### Gerald Higgins

##### Well-known Member
OK so just to be totally clear, in the sample data provided, EXACTLY WHICH entries should be highlighted ?

#### Rashie

##### Board Regular
 Thu Fri Sat Sun Mon Tue Wed Thu Market Departure Time CXR & FlightNumber Name 24 11 2016 25 11 2016 26 11 2016 27 11 2016 28 11 2016 29 11 2016 30 11 2016 01 12 2016 LONEGG 07:30 gina £7.00 £11.20 £7.50 £8.40 £7.00 £7.00 £84.00 £8.40 LONEGG 09:00 richard £125.00 £10.00 £12.00 £125.00 £6.00 £6.00 £6.00 £6.00 LONEGG 10:00 gina £24.70 £8.40 £9.20 £2.00 £11.20 £9.00 £84.00 £4.00 LONEGG 12:00 richard £125.00 £15.00 £12.00 £20.00 £10.00 £7.00 £7.00 £6.00 LONEGG 12:30 gina £24.70 £13.30 £9.00 £24.70 £11.20 £9.20 £84.00 £8.40 LONEGG 14:30 gina £24.70 £8.40 £7.00 £25.50 £9.20 £4.00 £75.00 £7.00 LONEGG 15:00 richard £15.00 £15.00 £10.00 £20.00 £10.00 £7.00 £7.00 £7.00

<tbody>
</tbody>

So it's just for Gina if there is a difference of more than £2 for any other ticket that is within an hour for the same day. I used £2 for a starting figure which proved to be a bit rubbish but the amount will be amendable.

Thanks again!

Rashie.

Last edited:

#### AOB

##### Well-known Member
Okay try this for starters - recommend checking this with a fine toothcomb as, like you say, it's complicated...

Some assumptions I've made :

• your data starts in A1 (so "Market" is in column A, "Name" is in column D and the dates start from column E and toward the right)
• the days are in row 1, the dates & column headers in row 2 and the actual information starts in row 3
• all the cells are formatted correctly (i.e. times in column B as "hh:mm", prices as numbers / currency etc.)
• the data is contiguous (i.e. no gaps or blank rows - and for every row of data, there is a value in column A (e.g. "LONEGG")
• the sheet is called Sheet1 (change accordingly)
If this is correct, try this :

1. Go to Formulas > Name Manager
2. Click New...
3. Name = Data
4. Refers to : =OFFSET(Sheet1!\$A\$1,1,0,COUNTA(Sheet1!\$A:\$A),COUNTA(Sheet1!\$2:\$2))
5. Click OK
6. Click Close
7. Select all the columns containing prices (e.g. columns E through L in your example)
8. Go to Home > Conditional Formatting > New Rule...
9. Select "Use a formula to determine which cells to format"
10. In the textbox for "Format values where this formula is true:" enter the following : =AND(\$D1="gina",IFERROR(MATCH(1,(OFFSET(Data,1,3,,1)<>"gina")*((ABS(OFFSET(Data,1,1,,1)-\$B1))<=(1/24))*((ABS(OFFSET(Data,1,(INDEX(COLUMN(E1),1)-1),,1)-E1))>=2),0),0)>0)
11. Click the Format... button and define whatever formatting you want (e.g. red fill etc.)
12. Click OK

Does that work?...

#### AOB

##### Well-known Member
In the example where you showed which cells should be highlighted, should the following not also be highlighted :

• Monday 28th @ 10am (Richard has a flight 1 hour earlier which is £5.20 cheaper)
• Tuesday 29th @ 10am (Richard has a flight 1 hour earlier which is £3 cheaper)
• Thursday 1st @ 10am (Richard has a flight 1 hour earlier which is £2 cheaper)

?...

#### Rashie

##### Board Regular
It seems to be, so thanks for the effort you put into that for me.
You know what's weird, I've never noticed the "name manager" before...! Learning something new every day!

Rich

#### AOB

##### Well-known Member
No worries - sure isn't that the whole point?

You could just select all the data and name it that way but the method above makes it dynamic - so if you add more dates to the right or times to the bottom, the named range automatically extends to include it (hence why I made a point of asking if the data was contiguous)

Replies
13
Views
232
Replies
4
Views
150
Replies
8
Views
267
Replies
6
Views
217
Replies
6
Views
271

1,190,913
Messages
5,983,532
Members
439,848
Latest member
timmyo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back