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?



.