2003 conditional format formula help please

[sideysid]

Hi,

After trawling through lots of posts I still havent quite found out if what Im looking for is possible.

I have set up a spreadsheet that has a macro set up to move a row to second 'complete' page when the status on a drop down list is set to closed. (Got the VBA code from here thanks guys)

What I also have been required to do, is colour code the various 'Actions'. Now this is easy in 2007 etc as Im sure you all well know, but 2003 3 rule limitation has been bugging me.

These 'Actions' are taken from a the 3rd worksheet tab, labeled 'Data' via data validation list, to keep everything out of the way of users and the transfer macro.

I only need three colours, so is three rules possible via the use of formulas? e.g. I need the 'Actions' such as 'in process', 'viewing arranged' to be amber and things like 'file closed' and 'viewing complete' to be green etc. I was thinking their maybe a formula that can include more than one statement in one rule but it has so far eluded my limited knowledge.

I have tried VBA macro code, to get around the 3 rule limitation, but its seems to interfere with the transfer code macro to the second spreadsheet, and me being a VBA novice has so far failed to make both macros work.

Any help would be greatly appriciated.

Thanks in advance

[Max, Singapore]

> ... I only need three colours, so is three rules possible via the use of formulas? (in Excel 2003)
Yes, in Excel 2003 you could zap up to 3 conditions to trigger 3 fill colors.
Actually its 4 colours, if you count the default fill color as well

[sideysid]

Ok thanks, could you let me know how I could apply more than one statement to a conditional format rule?

[Ace_XL]

See attached, CF has 3 rules

[sideysid]

Thanks, but Im not using values less than greater than etc.

The data validation is from a drop down list on another worksheet, and it takes the statements from there. my forumla which works fine so far for each statement is ="File on order", ="viewing complete" etc. I need to be able to put = statement 1 + statement 2 and apply the same colour to them both, if im making any sense.

I have around 10 different statements on the drop down list and I need to apply three differnt colours to them.

Thanks

[Max, Singapore]

Your scenario to CF does sound quite complex, but think here's some key starts for you ...

1. Use the Formula Is option in the CF. In the CF formula, use OR or AND expressions to check for the desired text combinations. Example, for say, Condition 1: =OR($B2="ST1",$B2="ST2")
would trigger the CF if B2 contains either textstring ST1 or ST2
Above presumes that the CF is applied on the same sheet

You could add-on other checks as may be required for Condition 2 and 3

2. From your liner > ... on another worksheet, guess you're trying to CF across sheets?
IIRC, in Excel 2003, you would probably be prompted that CF cannot work across sheets.

To circumvent this, you could use INDIRECT
As an example, suppose you want to apply CF in Sheet2 by looking at Sheet1,
eg: =OR(Sheet1!$B2="ST1",Sheet1!$B2="ST2")

If Excel 2003 throws you an error prompt, you could use this equivalent to apply the CF:
=OR(OFFSET(INDIRECT("'Sheet1'!B2"),ROWS($1:1)-1,)="ST1",OFFSET(INDIRECT("'Sheet1'!B2"),ROWS($1:1)-1,)="ST2")

---------------
Success? High five it here, click the star at the bottom left of my responses

[sideysid]

Hi Max,

I think you understand where Im heading, apart from I need to make the whole column of CF applicable (F$) on sheet 1, but I think your final formula you mentioned may be able to make it work as I do get the errors trying to conditional format across sheets.

Here is my sheet to make things easier.

Thanks

[Max, Singapore]

Looking at your file, you can make use of the 3 defined names that you have created for your DV. Incidentally, using defined names is another (neater) way to circumvent Excel 2003's error prompt for DVs and CFs when you need to do it across sheets

For your CF,
In your col F in the 2 sheets where you have your DVs,
Select F2 down (F2 active)
then apply these 3 formulae as your CF Conditions 1, 2, 3 (the order does not really matter here)
=COUNTIF(Red,$F2)
=COUNTIF(Orange,$F2)
=COUNTIF(Green,$F2)
Format as required for each condition, ok out, and it should work fine
-------------
Success at last? Wave it here, hit the little star at the bottom left of my responses

[sideysid]

Thanks Max!!! Your a genius and I could kiss you after pottering about with this half of last week!

Works like a charm, knew there had to be a formula for named ranges but didnt have a clue what it could be.

Thanks

[Max, Singapore]

welcome, glad it worked out fine for you. cheers