Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
Hello!
If you could help, I'd truely appreciate it. A B C D E F 1 P P P 2 3 4 I want to Conditionally Format all cells that if there is an apprearance of "P" more than twice in Row 1, it will turn the cells yellow. Please Help. Thank You in Advance CdotWin |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
Hi CDotWin,
Assuming you want to do this in VBA, try: Sub Tester() Dim Rng As Range Set Rng = Range("A1:D10") '<<<<<<<<<<<<<<<< CHANGE! With Rng .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, _ Formula1:="=COUNTIF($1:$1,""=p"")2" .FormatConditions(1).Interior.ColorIndex = 19 End With End Sub Change the range to suit your requirements. If you want to do this manually, try: Select the cells that you want potentially highlighted., Format | Conditional Formatting... | Condition1 | Formula Is | =COUNTIF($1:$1,"=P")3 | Format ... | Patterns ! Select your color | OK --- Regards, Norman "CDotWin" wrote in message ... Hello! If you could help, I'd truely appreciate it. A B C D E F 1 P P P 2 3 4 I want to Conditionally Format all cells that if there is an apprearance of "P" more than twice in Row 1, it will turn the cells yellow. Please Help. Thank You in Advance CdotWin |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
Hi CDotWin,
Try: Sub Tester() Dim Rng As Range Set Rng = Range("A1:d10") '<<<<<<<<<<<<<<<< CHANGE! With Rng .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, _ Formula1:="=AND(" & Rng(1).Address(0, 0) _ & "=""p"",COUNTIF(" & Rng.Row & ":" _ & Rng.Row & ",""=P"")2)" .FormatConditions(1).Interior.ColorIndex = 19 End With End Sub Change the range to suit your requirements. --- Regards, Norman "CDotWin" wrote in message ... Hey, Yes, I would like to do this in VB. What you gave me worked. I'm just wondering if I can only apply the conditional formatting to the cells that have the "P" actually in them and not the entire range. I also want to see if the conditional formatting is true for each row, not really a range of cells. Each row represents a date and I am trying to see if "P" shows up more than three times on that date. I hope I'm not confusing you. Thanks for your help in advance. --CDotWin-- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
Thanks that works perfectly!
I was wondering if there was a way I could, instead of applying the conditional formatting if there are more than 2 cell with the letter p, apply the conditional formatting based on another range of cells. For example A1 thru L1 contain the names of employees but each manager has a different number of employees. The letter p stands for personal time off that an employee took. I only want the comditional formatting to apply to the cells with the p in it if ten perent or more of one manager's employee takes Personal time off on one day. It's kind of like A1:L1 is employee names A2:A279 are work days of the year B2:I279 are were the P's are being entered i guess I'm trying to do a countif statement. Can you help me either in VB or Excel Thank you in advance --CDotWin-- "Norman Jones" wrote: Hi CDotWin, Try: Sub Tester() Dim Rng As Range Set Rng = Range("A1:d10") '<<<<<<<<<<<<<<<< CHANGE! With Rng .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, _ Formula1:="=AND(" & Rng(1).Address(0, 0) _ & "=""p"",COUNTIF(" & Rng.Row & ":" _ & Rng.Row & ",""=P"")2)" .FormatConditions(1).Interior.ColorIndex = 19 End With End Sub Change the range to suit your requirements. --- Regards, Norman "CDotWin" wrote in message ... Hey, Yes, I would like to do this in VB. What you gave me worked. I'm just wondering if I can only apply the conditional formatting to the cells that have the "P" actually in them and not the entire range. I also want to see if the conditional formatting is true for each row, not really a range of cells. Each row represents a date and I am trying to see if "P" shows up more than three times on that date. I hope I'm not confusing you. Thanks for your help in advance. --CDotWin-- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
If you have dates in column A, then the first employee name must be in B1, not
A1. Where are the manager's names? Are they in another row (maybe 3, just below the employee name?), or do you have a table of employees and managers somewhere else, so that you would have to use VLOOKUP on the employee name to get the manager name? On Fri, 6 Aug 2004 05:51:03 -0700, "CDotWin" wrote: Thanks that works perfectly! I was wondering if there was a way I could, instead of applying the conditional formatting if there are more than 2 cell with the letter p, apply the conditional formatting based on another range of cells. For example A1 thru L1 contain the names of employees but each manager has a different number of employees. The letter p stands for personal time off that an employee took. I only want the comditional formatting to apply to the cells with the p in it if ten perent or more of one manager's employee takes Personal time off on one day. It's kind of like A1:L1 is employee names A2:A279 are work days of the year B2:I279 are were the P's are being entered i guess I'm trying to do a countif statement. Can you help me either in VB or Excel Thank you in advance --CDotWin-- "Norman Jones" wrote: Hi CDotWin, Try: Sub Tester() Dim Rng As Range Set Rng = Range("A1:d10") '<<<<<<<<<<<<<<<< CHANGE! With Rng .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, _ Formula1:="=AND(" & Rng(1).Address(0, 0) _ & "=""p"",COUNTIF(" & Rng.Row & ":" _ & Rng.Row & ",""=P"")2)" .FormatConditions(1).Interior.ColorIndex = 19 End With End Sub Change the range to suit your requirements. --- Regards, Norman "CDotWin" wrote in message ... Hey, Yes, I would like to do this in VB. What you gave me worked. I'm just wondering if I can only apply the conditional formatting to the cells that have the "P" actually in them and not the entire range. I also want to see if the conditional formatting is true for each row, not really a range of cells. Each row represents a date and I am trying to see if "P" shows up more than three times on that date. I hope I'm not confusing you. Thanks for your help in advance. --CDotWin-- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
Another question. You say the employee names are in columns A:L. That's 12
people. Your concern with a fraction 10% implies that each manager has at least 10 employees. How many managers are there for the 12 people? If there is only 1 manager for all 12 employees, then 1 person is 8.3%, 2 people is 16.6%. Maybe a count of 2 is all you need. On Fri, 6 Aug 2004 05:51:03 -0700, "CDotWin" wrote: Thanks that works perfectly! I was wondering if there was a way I could, instead of applying the conditional formatting if there are more than 2 cell with the letter p, apply the conditional formatting based on another range of cells. For example A1 thru L1 contain the names of employees but each manager has a different number of employees. The letter p stands for personal time off that an employee took. I only want the comditional formatting to apply to the cells with the p in it if ten perent or more of one manager's employee takes Personal time off on one day. It's kind of like A1:L1 is employee names A2:A279 are work days of the year B2:I279 are were the P's are being entered i guess I'm trying to do a countif statement. Can you help me either in VB or Excel Thank you in advance --CDotWin-- "Norman Jones" wrote: Hi CDotWin, Try: Sub Tester() Dim Rng As Range Set Rng = Range("A1:d10") '<<<<<<<<<<<<<<<< CHANGE! With Rng .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, _ Formula1:="=AND(" & Rng(1).Address(0, 0) _ & "=""p"",COUNTIF(" & Rng.Row & ":" _ & Rng.Row & ",""=P"")2)" .FormatConditions(1).Interior.ColorIndex = 19 End With End Sub Change the range to suit your requirements. --- Regards, Norman "CDotWin" wrote in message ... Hey, Yes, I would like to do this in VB. What you gave me worked. I'm just wondering if I can only apply the conditional formatting to the cells that have the "P" actually in them and not the entire range. I also want to see if the conditional formatting is true for each row, not really a range of cells. Each row represents a date and I am trying to see if "P" shows up more than three times on that date. I hope I'm not confusing you. Thanks for your help in advance. --CDotWin-- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
Your right there isn't a need for me to do 10% because 2 works better for
any amount of an employees that a manager has. Thanks --CDotWin-- "Myrna Larson" wrote: Another question. You say the employee names are in columns A:L. That's 12 people. Your concern with a fraction 10% implies that each manager has at least 10 employees. How many managers are there for the 12 people? If there is only 1 manager for all 12 employees, then 1 person is 8.3%, 2 people is 16.6%. Maybe a count of 2 is all you need. On Fri, 6 Aug 2004 05:51:03 -0700, "CDotWin" wrote: Thanks that works perfectly! I was wondering if there was a way I could, instead of applying the conditional formatting if there are more than 2 cell with the letter p, apply the conditional formatting based on another range of cells. For example A1 thru L1 contain the names of employees but each manager has a different number of employees. The letter p stands for personal time off that an employee took. I only want the comditional formatting to apply to the cells with the p in it if ten perent or more of one manager's employee takes Personal time off on one day. It's kind of like A1:L1 is employee names A2:A279 are work days of the year B2:I279 are were the P's are being entered i guess I'm trying to do a countif statement. Can you help me either in VB or Excel Thank you in advance --CDotWin-- "Norman Jones" wrote: Hi CDotWin, Try: Sub Tester() Dim Rng As Range Set Rng = Range("A1:d10") '<<<<<<<<<<<<<<<< CHANGE! With Rng .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, _ Formula1:="=AND(" & Rng(1).Address(0, 0) _ & "=""p"",COUNTIF(" & Rng.Row & ":" _ & Rng.Row & ",""=P"")2)" .FormatConditions(1).Interior.ColorIndex = 19 End With End Sub Change the range to suit your requirements. --- Regards, Norman "CDotWin" wrote in message ... Hey, Yes, I would like to do this in VB. What you gave me worked. I'm just wondering if I can only apply the conditional formatting to the cells that have the "P" actually in them and not the entire range. I also want to see if the conditional formatting is true for each row, not really a range of cells. Each row represents a date and I am trying to see if "P" shows up more than three times on that date. I hope I'm not confusing you. Thanks for your help in advance. --CDotWin-- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I convert conditional formatting into explicit formatting? | Excel Discussion (Misc queries) | |||
Formatting Conditional Formatting Icon Sets | Excel Discussion (Misc queries) | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions |