conditional formatting for multiple values

[aravindhan_31]

hi,

can anyone tell me how do i write code for multiple conditional formatting,

for ex: if Cell A1 = Peronal Details then cell color shud be light blue,
if A1= Branch Details then cell color shud be light blue
and so on
I have a list of 17 items, if that items are entered in cel A1 the backround shud be light blue...

can anyone help me...

 

[SteveO59L]

You could write a long Or() statement

=or("Peronal Details","Branch Details",etc.....)

Alternatively, populate a cell with all of the possibilities and use find()

 

[aravindhan_31]

Hi,

Thanks for your reply,,

can you tel where shud i use this, I used in conditional formatting, it didnt work....

 

[Peter_SSs]

aravindhan_31

Here is one way to achieve what you want (at least as I understand it).

Put the list of items in a columns somewhere. I have used just a few items in column E and the column could be hidden if you want.

Then:
1. Select A1
2. Format|Conditional Formatting...|Condition 1|Formula is: =MATCH(A1,E1:E16,0)|Format...|Patterns|choose blue|OK|OK

Excel Workbook
	A	B	C	D	E
1	Other Details				Personal Details
2					Branch Details
3					Other Details
4					My Details
5					Your Information
6
Conditional Formatting

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1	1. / Formula is =MATCH(A1,E1:E16,0)	Abc

 

[aravindhan_31]

Hi Peter

Thanks,

could you please help me again on the same,

from your example If I enter Branch Details on cel A1 color is changing to blue,
but n If I enter any values from the list, in anywhere in Column A that has to be changed to blue,

for example if I enter Other detailsin Column A1 the color is blue
If I enter Personal Details in Cell A10, that also to be changed to blue,

to be clear, I have a list of items, if any of the item on that list entered in anywhere in Column A, all those items shud be marked in blue, anything other than these items shud be displayed normally.

 

[Peter_SSs]

Hi Peter

Thanks,

could you please help me again on the same,

from your example If I enter Branch Details on cel A1 color is changing to blue,
but n If I enter any values from the list, in anywhere in Column A that has to be changed to blue,

for example if I enter Other detailsin Column A1 the color is blue
If I enter Personal Details in Cell A10, that also to be changed to blue,

to be clear, I have a list of items, if any of the item on that list entered in anywhere in Column A, all those items shud be marked in blue, anything other than these items shud be displayed normally.

1. Select column A by clicking its heading label.
2. In Conditional Formatting formula put =MATCH(A1,E$1:E$17,0) choose colour etc and OK|OK

 

[aravindhan_31]

Thanks its perfect

 

[Peter_SSs]

It's a pleasure, thanks for the feedback.

 

[aravindhan_31]

need help for another macro

I need to do a audit payrool process.
number of persons - 4

Process A has 5 sub processes.
Process B has 7 sub processes and so on

there are about 20 main processes and subprocess for every process.

I have created a validatation in Column "I11" with the list of all the main process

and Column "Q11" all sub processes using vlookup

If I select Process A from the validation list in "I11", in column from Q11 all the sub process will come automaticaly one after the other,
If I select Process B from the validation list in "I11", in column from Q11 all the sub process will come automaticaly one after the other,

and so on

Basically I have below columns,

Column A1 = Name of the Auditor ( person who works on this sheet
Column B1 = Name of the Processer( for this person the auditor cheks)

Column I11 - Main process
Column Q ( From Q11 - Sub processes of Main processes one after the other
Column R - Status, that is0"Correct" or "in correct"
Column S - Score (0.5 or 1)

flow:

Select any item from the list of cell "I11"
sub process will be populated from "Q11" one after the other,
and enter as Correct or Incorrect in Column R
and enter the score as 0.5 or 1 in column S against the item.


Once I enter all the above details on their respective column. i need a macro where if I click save all the details such as
Auditor name, name of the processer, Main process, Sub process, status(correct or incorrect), score has to be saved in the next worksheet "Audited"

Again I will audit for the same person for the different process ( process B) then again i will get sub processed n update status, scores etc., again if i click save the same has to be saved on the next sheer after the first process in the worksheet "Audited"
same way i will audit all the process for the same person. once all the audit is done for this person, the worksheet "Audited" has to be saved as file with the
processer name on a different folder. ( ex if I am auditing for a person A) a file has to be saved with the name A.


then I click Clear on the sheet, which clears all the feilds so that i can audit another person and do the same procedure..



Can you please help me on this

 

[Peter_SSs]

Re: need help for another macro

This is a different question and really should be in its own thread. Best to stick to the one already started here:
http://www.mrexcel.com/forum/showthread.php?t=308433

No answer often means that readers cannot understand exactly what you require. I suggest you make a reply yourself to that other thread and try to explain again in different words. Also, if possible, a bit shorter as many people don't bother to read long questions too closely.

Also, by replying to the thread it will come to the top of the list again. It is OK to do that so long as you leave a reasonable amount of time between "bumps" and don't do it too many times.

Good luck!