Thanks Bernie!
Here is an update, (the final solution!)
It would have been a bit impractical to move columns together and what is
more the three helper columns (for each colour) became four (for each colour)
to allow for additional customers and may one day become 5 etc. so after
numerous attempts I managed to simplify the cf formulas to test for 1 odd
column down to
=ISODD(SUM($W2;$AD2;$AK2;$AR2))
and to test for 1 even column
=AND(ISEVEN(SUM($W2;$AD2;$AK2;$AR2));SUM($W2;$AD2; $AK2;$AR2)0)
For some reason this way does not require ISERROR parameters (which is great)

So I am quite pleased that it all came down to something very simple even
though there were complications on the way!
Thanks again for your help.

"Bernie Deitrick" wrote:

goodfish,

I think your formula may not give you the result you think it will. Try
moving your three columns together, say, starting in AK, and use the array
formula (enter using Ctrl-Shift-Enter)

=SUMPRODUCT(NOT(ISERROR(AK2:AM2))*(MOD(AK2:AM2,2)= 1))0
=SUMPRODUCT(NOT(ISERROR(AK2:AM2))*(MOD(AK2:AM2,2)= 0))0


in two cells - say, AN2 and AM2, and simply use

=AN2
and
=AM2

as your CF formulas.

HTH,
Bernie
MS Excel MVP


"goodfish" wrote in message
...
Hi Bernie and thanks for the help.
I have modified the helper columns a bit and avoided referencing the
customer name all together in the cf formulas.
Now, to highlight a row I just have a cf formula to check whether one of
three helper columns is odd (apply one colour shade) and one cf formula to
check whether one of them is even (apply other colour shade). If any of
the
helper columns is neither odd or even the helper columns will still
contain a
formula so I also need the cf formula to pick up errors and if so ignore
them
and check the other helper columns.
Don't know if it can be simplified further but I have come up with the
following formula.
=IF(ISERROR(ISODD($V2));ISODD($AD2);IF(ISERROR(ISO DD($AD2));ISODD($V2);ISODD($AK2)))

Thanks again.

"Bernie Deitrick" wrote:

goodfish,

You have a lot of options - you could enter a keyword into a helper
column:

=AND($AH2="New";ISEVEN($AG2))
=AND($AH2="New";ISODD($AG2))

You can get as creative as you want, as long as your formula returns True
or False: Enter a date and
use that value to determine if the customer is new... something like

=AND($AH2="New";ISEVEN($AG2);$AI2(TODAY()-7))

to only highlight new customers who have done something in the last 7
days...and on and on...

HTH,
Bernie
MS Excel MVP


"goodfish" wrote in message
...
Hello again!
I was just reading my post again and an idea sprung to mind.
On the right of my tables/list there is a helper column for each
customer....in the example I provided the helper column is AG where
cell AG1
is the "helper column header row" and it contains the customer name.
for new
customers I have already set up the helper columns with the header as
"newa",
"newb" etc.
So now if instead of "newa" I make the CF formula search an indefinite
value
in AG1 then the conditional formating should still work.
What would be the best formula to use?


"goodfish" wrote:

Hi Bernie!
I was thinking it might be complicated but I was trying to avoid the
user to
have to mess around with conditional formulas!
Basically I have 3 tables on three different sheets and a list on the
fourth
sheet.
These keep track of orders, contracts, shipments and invoices etc.
To make the data more easily leggible I have set up CF formulas to
highlight
rows with a different colour for each customer, in fact I use 2 shades
of the
same colour for each customer so to distinguish two succesive
contracts from
any one customer.
I have also set up additional CF formulas so that when a new customer
is
inserted all I have to do is replace the e.g. "newa" part of the
formula with
a new customer name.
These are the formulas for one sheet (they refer to a helper column):
=AND(ISNUMBER(SEARCH("*newa*";$A2));ISEVEN($AG2))
=AND(ISNUMBER(SEARCH("*newa*";$A2));ISODD($AG2))
For the first sheet both formulas apply to:
=$A$2:$R$40
This range changes automatically as rows are inserted in the
tables/list.
The last sheet has an additional formula to be modified which is:
=ISNUMBER(SEARCH("newa*";$D$35))
Does this help?
The only other way around I can think of is to use vba to make up the
formulas and cf rules but that sounds even more complicated.




"Bernie Deitrick" wrote:

It is unnecessarily complicated. Use a CF formula that doesn't need
to be changed, one that
references a cell, range, or dynamic named range. Post what your
criteria for the CF is
currently,
and we can help you with that.

HTH,
Bernie
MS Excel MVP


"goodfish" wrote in message
...
Hello!
I am wondering if it is possible to replace text of a CF formula
with new
text given by the user through an input box .
I need it to do this throughout every sheets' CF formulas within a
workbook
and every time a new customer is added to the spreadsheet.
I have already made the CF formulas for new customers by entering
newa,
newb, newc......newp which need to be replaced with customer
names. And I
have already set the range on which to apply the CF rules.
Any ideas?



.



.


.