(Perhaps) Conditional Formatting - Tabs

**DMcC** · 03-19-2013, 04:46 AM

Morning all,

Brand new to the forum, and a complete novice so appreciate your patience in advance!

I am creating a resourcing tool, based by maximum man days available against those utilised by Project. There are separate tabs for each resource type, as things stand (this the preferred option of the powers that be.)

All is fine, and I am happy with the end product as it stands - with 2 exceptions. Where resource usage is in excess of 100% (ie any cell value greater than 100%) , I wish the cell itself to turn a suitably fetching colour to highlight this fact. For the excel boffins out there, I can but presume this will be an easy fix!

However, in addition I would also like the relevant tab featuring cells in excess of 100% to also feature said colour...which may pose more of an issue.

Any assistance on this is greatly appreciated. Pretty much lost as things stand.

Kind regards,

DMcC

**DMcC** · 03-21-2013, 07:43 AM

Shameless BUMP......

Any miracle workers out there?

**BB1972** · 03-21-2013, 10:15 AM

Hi DMcC, welcome to the forum. If you could upload a small sample workbook that shows us the layout of your data (with any confidential data removed - mock it up if necessary), you shouldn't be waiting too long for a solution.

**FDibbins** · 03-21-2013, 10:21 AM

changing the cell color is easy, use conditional formatting...home tab, styles/conditional formatting, select new rule, select "format only cells that contain, select "greater than"

As for changing the tab color, that will require VBA, and my VBA sucks

**DMcC** · 03-21-2013, 10:47 AM

Many thanks Brendan - appreciate that. Have taken template and now filled it in with some dummy information.......it is no longer percentage based; rather based on man hours.

I am, in effect, wanting the spreadsheet to be intuitive - so if a resource is over allocated (ie, as an example on PE sheet cells B47/C47/D47/E47/F47 combined total a number greater than cell G47, then G47 automatically goes red, while the tab colour does as well.....however, sadly for the life of me I have no idea how to do it.

Any assistance that could be supplied would be absolutely fantastic.

Kind regards,

DMcC

**DMcC** · 03-21-2013, 10:51 AM

Originally Posted by FDibbins

changing the cell color is easy, use conditional formatting...home tab, styles/conditional formatting, select new rule, select "format only cells that contain, select "greater than"

As for changing the tab color, that will require VBA, and my VBA sucks

Well, you at least know of the EXISTENCE of VBA mate, which puts you a step above me, rest assured!

**DMcC** · 03-22-2013, 03:53 AM

Morning all (over here it is, anyway.....)

ANother night spent trying to figure this all out, without success. Again, any help that could be offered would be great.

**BB1972** · 03-22-2013, 09:24 AM

Hi, DMmC - can you clarify which cells you're referring to in (your) post # 5? I've had a look at your workbook, and cells B47:F47 on the PE tab are blank; and Column G contains nothing at all.

Did you try FDibbins advice regarding the use of conditional formatting to highlight particular cells?

**DMcC** · 03-22-2013, 09:31 AM

Hi Brendan,

Apologies - that was silly of me. I plucked those cell numbers out of thin air purely as an example. In effect, if B13:E13 when totalled > number in F13, I would like F13 to go red. Equally, if < number in F13, I would like it to green.

Concerning FDibbins generous offer of assistance, believe it was based on an initial query which related to percentages...current table is now set up as man hours, which can range from, say, 22 to 75 - as such, I thought the simplified rule relating to a single percentage number (100%) would not apply?

**BB1972** · 03-22-2013, 03:13 PM

Originally Posted by DMcC

Apologies - that was silly of me. I plucked those cell numbers out of thin air purely as an example.

No problem at all, but bear in mind that volunteers' time is generally not best spent on guesswork - the more accurate a post is, the better chance that someone can understand what exactly is required, in order to assist. This advice applies to everyone - myself included

So - providing a solution to changing the cell colours is straightforward. There may be an element of over-thinking FDibbins' suggestion - greater than is greater than, regardless of the units used. The same formula will work for both percentages and numbers, as percentages are effectively just that - numbers.

I have applied Conditional Formatting to F1:F46 on your PE tab. The formula:

=SUM(B2:E2)>F2

is a "rule" for Excel to work with - the formula part just says (in rather shoddy English) "equals the sum of cells B2:E2 are greater than F2's value". By using this formula within a rule in CF, we tell Excel to apply the formatting - in this case, a red fill - if the rule is met - the sum of B2:E2 are greater than the value of F2.

Regarding changing the tab colour, my VB sucks even more than FDibbins. However, in the absence of another solution, perhaps the following workaround will be useful.

I have used Column G in the PE tab as a helper column, which you can hide. There is a formula in F1:

=IF(SUM(B1:E1)>F1,1,0)

- "if the sum of B1:E1 is greater than the value of F1, return 1, otherwise, return 0". Therefore, only resources that go over the days available will return a 1.

Then, in Column I on your Totals tab, I have listed the other tabs, and have used another CF rule:

=SUM(PE!$G:$G)>0

to format the cell with a red fill if the sum of G:G on the PE tab is greater than 0 - ie, they've gone in excess of the days allocated. This way, any tabs which you need to identify are identified on the Totals tab.

I hope this helps.

**DMcC** · 03-25-2013, 04:53 AM

Good morning Brendan,

Firstly, may I thank you sincerely for the time you have taken with regard to a solution to the issues I was encountering.

I have, this morning, implemented all on to my most recent revision of resourcing sheet, with the correct data in, and it is working perfectly.

Hugely appreciated.

Kind regards,

DMcC

**DMcC** · 03-25-2013, 05:07 AM

Good morning Brendan,

Firstly, may I thank you sincerely for the time you have taken with regard to a solution to the issues I was encountering.

I have, this morning, implemented all on to my most recent revision of resourcing sheet, with the correct data in, and it is working perfectly.

Hugely appreciated.

Kind regards,

DMcC

**BB1972** · 03-25-2013, 06:58 AM

No problem, DMcC - glad to be of assistance.

You might help us to keep the forum tidy and use the thread tools to mark your thread "Solved", provided of course you're satisfied that it is.

(Perhaps) Conditional Formatting - Tabs

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

(Perhaps) Conditional Formatting - Tabs

Re: (Perhaps) Conditional Formatting - Tabs

Re: (Perhaps) Conditional Formatting - Tabs

Re: (Perhaps) Conditional Formatting - Tabs

Re: (Perhaps) Conditional Formatting - Tabs

Re: (Perhaps) Conditional Formatting - Tabs

Re: (Perhaps) Conditional Formatting - Tabs

Re: (Perhaps) Conditional Formatting - Tabs

Re: (Perhaps) Conditional Formatting - Tabs

Re: (Perhaps) Conditional Formatting - Tabs

Re: (Perhaps) Conditional Formatting - Tabs

Re: (Perhaps) Conditional Formatting - Tabs

Re: (Perhaps) Conditional Formatting - Tabs

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions