EXCEL

You can use conditional formatting -

Under the Format Menu, choose Conditional Formatting - a dialogue box will appear and you can set your condition, and under 'format' change the color to red.

Although a good thought, this probably won't meet the lady's needs. The copy of Excel I am using in this machine is a few years old but the cell "conditional format" is limited to the applied cell only and is basic statements like equal to, less than, between, etc.. My copy does not include anything that would do what Sara Lens is asking.

Maybe...doubt it...but maybe the latest Excel has expanded the conditional format statements do Column statements

Sara... to do what you want ... verify no double or repeat entries in a column ... you may need to come up with a VB script attached to the protected cells "on change" or "on update" properties that will look at the column and do an equals check on the range of cells.

Good point - I was thinking of Excel 2007 where:

One new feature of the Conditional Formatting tool in Microsoft Excel 2007 is the ability to highlight duplicate values in a range. To do this, select the range and choose Conditional Formatting > Highlight Cells Rules and choose Duplicate Values. You can highlight cells that have duplicate values by selecting Duplicate or choose Unique to highlight cells which contain unique values.

Select the format to use and click OK. Now, all cells containing duplicate values will be highlighted using the selected format. If data changes and duplicates are created later on, the conditional format will ensure these cells are highlighted.

Otherwise, would it be possible to install a condition of a formula - perhaps using a 'countif' to determine if there are duplicates?

It is getting late here and my brain isn't working too well.

In Excel 2003 and below, u can use cond format up to 4 conditions only UNLESS u use a formula to create other conditions. Try to combine conditinal formatting, data validation and dynamic range.

Hello Switchman:

Just to confirm, I have Excel from Office 2003. It to has very basic formatting choices.

I got into about two years ago but have not used it much since. Then I am pretty sure I wrote my 'instruction' to format it, but, quite what it was I can't recall.

i really appreciate your concern but i have tried format methode and did nothin

if VB can work iam not good in dealin with it so if a certain code can help plz write in details

i am still tryin and i am honoured with your help

I assume then that you do not have Excel 2007, correct?

I tried this with my Office Excel 2003 (XP) version, and it does work splendidly!

(Be sure to adjust the cell references to the spreadsheet cell ranges you are wanting to affect.)

Thanks, and maybe someday I'll get a newer version...

Curtis

Hi all,

One improvement over "Guest"'s formula that I often use is:

1. select the first cell in your range (let's assume A2)

2. Choose Format>Conditional Formatting

3. From the dropdown, choose Formula Is

4. For the formula, enter:

=COUNTIF($A$1:A1,A2)>0

5. , 6. and 7. as in "Guest"'s example

To apply the formula to your range, independently of how many rows you have, all you have to do is just highlight the cell you just formatted (I've assumed A1, but could be another), copy it and then select the whole range you want the format applied to and select Edit>PasteSpecial>Formats. If you've paid attention to entering the "$" signs exactly the way I've described them, then the conditional formatting formula should update itself automatically, and include the whole range. This way if you increase or decrease the number of rows of data there's no need to change the conditional formatting in any way, just copy and paste it :D

Note that for this to work your data range must start at least in row2.

Hope this helps!

hi Sara

i can mail you excel tutorial modules i received during my tenure as a black-belt with caterpillar if you are keen mail me your Id

crm

CRM,

Pls mail me that tutorial and thanks.

Nachi (e-mail: nach_sam@yahoo.com)

Dear there is "conditional formatting" option under "Format" heading,you will find your solution there.

'count number of rows
'if 1500, then set color

rowCtr = 4
Do Until Cells(rowCtr, 1) <> ""
If Cells(rowCtr, 4) = "1500" Then Cells(rowRtr, 4).Interior.ColorIndex = 36
rowCtr = rowCtr + 1
Loop

I can't understad.

Please give deeply information, at which place we have to write bottom syntax.

rowCtr = 4
Do Until Cells(rowCtr, 1) <> ""
If Cells(rowCtr, 4) = "1500" Then Cells(rowRtr, 4).Interior.ColorIndex = 36
rowCtr = rowCtr + 1
Loop

Hello chrisg288:

Just to say I like the way you explained how to work with and in Excel including with Macros and VBA. Learned a few things there, thanks.

Take care,

sorry.. the button for macros is on the forms toolbar.. not the controls toolbar.

ty

chris

Hello chrisg288:

Thanks for getting back to me. I have not made macros in Excel but am pretty sure I have made at least one in word?

It may be that I have been reading about and got mixed up with read and learning, as apposed to actually doing......making a macro.

I do not recall seeing a 'button' for macros. I thought I had seen somewhere, perhaps in help or a magazine, how you put a macro together. As I type I am trying (unsuccessfully) to nail the details of the items floating in my mind!

I do not have a good recall for what all the different windows and pop up info windows are called, so I cannot fathom what you mean by "the macro button is on the forms toolbar, not the controls toolbar.

I use things and do thing and go places on my computer and know what I want to achieve and am usually successful............By the Form toolbar, is that in Excel, and does Word have a similar Forms or Templates toolbar, on the right of the screen?

I would be obliged if you could give advice on the meaning of the two toolbars please? I started using Excel maybe 8 months ago. And can pretty much do what I want. But, if I go back there now after a couple of months not using it can take me an hour to 'find everything'?

I will drop you a line to explain better.

Take care........

here is a link to excel tut that features some conditional formatting for gantt.

Only option is Conditional Formatting

In that u make formula: if condition link with that cell value = somenumber will be formatted as like

Clement

Hello mr_chowking:

Ok lady, tell me exactly what u need, maybe I could be a help.

Just one thing........

This is a site where anyone, men and women ask others who may know, to help them.

It is not a 'one line' chat-up bar.

There is no way you would have said that to a man so why, on your very first post do you totally screw it, and say that kind of thing to a girl?

You are on the wrong site. If you have manners now, on this site, is the time to use them.

Do you honestly think Sara would reply to you. It was just rude.

Hello Guest,

my post was in response to a sexist remark made by a previous poster, which are hi-lighted.

They were not my remarks. It is the man who made the sexist remark you should be sending this to not me. I certainly did not have my foot in my mouth as you put it. The sexist remarker most definately did, actually both feet, as it was his first post anywhere on this site. Sign in and lets see your colours.

Okay let's try it your way.

To Mr Chowking:

Please to consider the foot extricated and removed from babybear's mouth, and put into your own mouth!

To the honorable GUEST

How to extricate foot in XL2003:

Ctrl + F = Find, Ctrl + U = Underline, Ctrl + C = Copy, Ctrl +K = Insert Hyperlink

and another Ctrl + U to underline all! Thanks

Please to consider the foot extricated and removed from babybear's mouth, and put into your own mouth!

Sorry, babybear. MrChowking says to keep foot and take his foot...and whatever...as well. Oh, well. You musta been right... cuz he's still riled about it.

Me just keep tongue in cheek.

i'm not a frequent excel user but i have this Excel 2007 Bible e-book

msg me if u want a copy

eworque,

Pls mail me that e-book and thanks.

Nachi (e-mail: nach_sam@yahoo.com)

Send me an email to my box and will send you the visual basic program to do it.

Hi Sara,

There is a function called Conditional Formatting that you can find in the Format drop down menu. It's self explanatory and basically you set the conditions to trigger an effect as well as the effect itself.

Sara Lens,

Another easy way (regardless of which version of Excel you're using) is to simply 'Find and Replace' (press ctrl+F). In this menu, you can search for the value, and replace the cell with the same value but having 'Fill'ed the cell with any color or other method of formatting. The one downside to this is that it must be re-done after each time the sheet is updated as it is not a 'live updating' method. (To re-do this, either clear all formatting from the sheet (from the Home > Clear > Clear Formats tab in Excel '07, with removes all underlining, coloured cells, etc in the sheet), or Find and Replace (F+R) what you changed the cells to in your previous search back to its original formatting (i.e. F+R your value with the colour you selected, replace it with the value and no fill). Now, F+R the new value, and change it to the new colour.)

Hope this helps. Excel is a pretty powerful tool once understood.

-MechMatt

Hi to all,

excuse me Sara, but has anyone else been having problems with the spell-checker today? It just will not work for me and freezes as I try to check.

It may be to do with my computer so I will look into it if no one has had the same problem.

Take care

Try this one:

For XL2003

1. COLOR DUPLICATE CELL:

Click Insert>Name>Define

Define Name as "Dyna"

On the "Refers To" Box type

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

Click OK...

Click Format>Conditional Formatting>Formula Is......

=COUNTIF(DYNA,A1)>1

and the formatting .... (back color, font color etc etc.)

Close

Note: In this case there is no need to specify or highlight the range, row is automatically added in the range once it is filled with data.

2. AVOID ENTERING DUPLICATE VALUE:

Click Tools>Data Validation....In Allow Box click "Custom"

In the "Formula Box" type..

=COUNTIF($A:$1,A1)=1

Click "Error Alert Tab"..... On "Error Alert" type

DUPLICATE VALUE! or whatever alert pop up note u want..

Click OK..... Ctrl+C cell A1 (highlight) and paste it to desired range in Col.A

try it! Let me know if it works.....Einshala!

Sorry for my English!

It is the same in XL2007 aside from different location of menus...

Neat! Quite ellegant

Hello mr_chowking:

Just to say I like your Excel explanation. And may well use it myself.

Yes -

Use your pull down menu to select FORMAT

Select CONDITIONAL FORMAT

set each cell to according to the condition (=, <, <=, >, NOT...) - this can be another cell or specific value.

and then select the Format button in the bottom right corner - this will allow you to set the font colour, size, cell background or cell border.

Did you search out past links?.....

http://cr4.globalspec.com/thread/14872#newcomments

Other than that there are some very good comments that were added to these posts

yes of course.

hi,

i've been trying the conditionnal format but a message appears everytime sayin that there is an error in formula !!!

thnx for ur efforts and i am still in need for ur help urgently

if anybody wanna send me a tutorial my mail is:

sara_elusive@yahoo.com

thnx in advance

Hello Sara,

just to say it is not a good idea to publish your email in open forum. It is just asking for spam. I hope you will be OK, but, you can never be sure.

You can alway write to admin to ask for it to be removed. If you want that is?

oppps

u r totaly right!

hey all

can i ask a question

the method of the duplicate alert is good one but the formula is not correct i think

but i wanna know if this method will work after entering a duplicate value not in specific range?

thnx in advance