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

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

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--

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--

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--

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--

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--