Change cell format based on condition.
 

I want to change B5 cell format to a red fill if any of the cells in the
range B6-N145 has a cell fill format of red.

Change cell format based on condition.
 

How will the individual cell get the red fill?

If it's through conditional formatting you could extend the conditiona
formatting over the range.

If it's any other way I think you would require VBA code

--
Nori
-----------------------------------------------------------------------
Norie's Profile: http://www.excelforum.com/member.php...fo&userid=1936
View this thread: http://www.excelforum.com/showthread.php?threadid=38410

Change cell format based on condition.
 

Assuming it is not conditional formatting

For Each cell In Range("B6:N145")
If cell.Interior.Colorindex = 3 Then
Range("B5").Interior.Colorindex = 3
Exit For
End If
Next cell

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Patrick Simonds" wrote in message
...
I want to change B5 cell format to a red fill if any of the cells in the
range B6-N145 has a cell fill format of red.

Change cell format based on condition.
 

I am not totally sure I understand your question.

The range B6-N145 has cells which are format with a fill color of red if
certain conditions are not met. There are a number of conditions being
checked (each cell has it's own conditional format). What I need is to have
cell B5 be formatted with a red fill if any of the cells in the above range
are filled red. There are to many conditional formats being applied to the
range B6-N145 to applied them all to cell B5.


"Norie" wrote in
message ...

How will the individual cell get the red fill?

If it's through conditional formatting you could extend the conditional
formatting over the range.

If it's any other way I think you would require VBA code.


--
Norie
------------------------------------------------------------------------
Norie's Profile:
http://www.excelforum.com/member.php...o&userid=19362
View this thread: http://www.excelforum.com/showthread...hreadid=384101

Change cell format based on condition.
 

I should also add that I would also want it to remove the red fill from cell
B5 if there were no longer any cells in range B6-N145 with a red fill
pattern.


"Patrick Simonds" wrote in message
...
I am not totally sure I understand your question.

The range B6-N145 has cells which are format with a fill color of red if
certain conditions are not met. There are a number of conditions being
checked (each cell has it's own conditional format). What I need is to
have cell B5 be formatted with a red fill if any of the cells in the above
range are filled red. There are to many conditional formats being applied
to the range B6-N145 to applied them all to cell B5.


"Norie" wrote in
message ...

How will the individual cell get the red fill?

If it's through conditional formatting you could extend the conditional
formatting over the range.

If it's any other way I think you would require VBA code.


--
Norie
------------------------------------------------------------------------
Norie's Profile:
http://www.excelforum.com/member.php...o&userid=19362
View this thread:
http://www.excelforum.com/showthread...hreadid=384101

Change cell format based on condition.
 

I should mention that I also want to have the red fill removed from cell B5
once there are no cells formatted red in the range B6-N145. The ultimate
goal is to have no red cells on the worksheet.


"Patrick Simonds" wrote in message
...
I am not totally sure I understand your question.

The range B6-N145 has cells which are format with a fill color of red if
certain conditions are not met. There are a number of conditions being
checked (each cell has it's own conditional format). What I need is to
have cell B5 be formatted with a red fill if any of the cells in the above
range are filled red. There are to many conditional formats being applied
to the range B6-N145 to applied them all to cell B5.


"Norie" wrote in
message ...

How will the individual cell get the red fill?

If it's through conditional formatting you could extend the conditional
formatting over the range.

If it's any other way I think you would require VBA code.


--
Norie
------------------------------------------------------------------------
Norie's Profile:
http://www.excelforum.com/member.php...o&userid=19362
View this thread:
http://www.excelforum.com/showthread...hreadid=384101

Change cell format based on condition.
 

I Tried this and it seems to work well using a Worksheet_SelectionChange,
but I can not get cell B5 to return to a no fill color once the cells in the
Range B6:N145 are no longer red.

In other words when the condition which turns a cell red in the range
B6:N145 (it indicates a problem), corrected the red fill goes away and I
then want the red fill in cell B5 to go away.


"Bob Phillips" wrote in message
...
Assuming it is not conditional formatting

For Each cell In Range("B6:N145")
If cell.Interior.Colorindex = 3 Then
Range("B5").Interior.Colorindex = 3
Exit For
End If
Next cell

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Patrick Simonds" wrote in message
...
I want to change B5 cell format to a red fill if any of the cells in the
range B6-N145 has a cell fill format of red.

Change cell format based on condition.
 

Range("B5").Interior.Colorindex = xlColorindexNone
For Each cell In Range("B6:N145")
If cell.Interior.Colorindex = 3 Then
Range("B5").Interior.Colorindex = 3
Exit For
End If
Next cell


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Patrick Simonds" wrote in message
...
I Tried this and it seems to work well using a Worksheet_SelectionChange,
but I can not get cell B5 to return to a no fill color once the cells in
the
Range B6:N145 are no longer red.

In other words when the condition which turns a cell red in the range
B6:N145 (it indicates a problem), corrected the red fill goes away and I
then want the red fill in cell B5 to go away.


"Bob Phillips" wrote in message
...
Assuming it is not conditional formatting

For Each cell In Range("B6:N145")
If cell.Interior.Colorindex = 3 Then
Range("B5").Interior.Colorindex = 3
Exit For
End If
Next cell

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Patrick Simonds" wrote in message
...
I want to change B5 cell format to a red fill if any of the cells in
the
range B6-N145 has a cell fill format of red.

Change cell format based on condition.
 

Well that worked on my test sheet, but, now I know why Conditional
formatting was mentioned. The cell format (red or no color) in the range
B5:N145 are controlled by conditional formatting. So when the code looks at
the cells that are red, they do not appear to be have any cell formatting
assigned.

Am I just out of luck?


"Bob Phillips" wrote in message
...
Range("B5").Interior.Colorindex = xlColorindexNone
For Each cell In Range("B6:N145")
If cell.Interior.Colorindex = 3 Then
Range("B5").Interior.Colorindex = 3
Exit For
End If
Next cell


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Patrick Simonds" wrote in message
...
I Tried this and it seems to work well using a Worksheet_SelectionChange,
but I can not get cell B5 to return to a no fill color once the cells in
the
Range B6:N145 are no longer red.

In other words when the condition which turns a cell red in the range
B6:N145 (it indicates a problem), corrected the red fill goes away and I
then want the red fill in cell B5 to go away.


"Bob Phillips" wrote in message
...
Assuming it is not conditional formatting

For Each cell In Range("B6:N145")
If cell.Interior.Colorindex = 3 Then
Range("B5").Interior.Colorindex = 3
Exit For
End If
Next cell

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Patrick Simonds" wrote in message
...
I want to change B5 cell format to a red fill if any of the cells in
the
range B6-N145 has a cell fill format of red.

Change cell format based on condition.
 

That's why we asked <vbg

Try this version, assuming that the CF is set by a formula not a straight
condition

Range("B5").FormatConditions.Delete
For Each cell In Range("B6:N145")
If cell.FormatConditions.Count 0 Then
If Evaluate(cell.FormatConditions(1).Formula1) Then
With Range("B5")
.FormatConditions.Add Type:=xlExpression,
Formula1:="=TRUE"
.FormatConditions(1).Interior.ColorIndex = 3
End With
Exit For
End If
End If
Next cell


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Patrick Simonds" wrote in message
...
Well that worked on my test sheet, but, now I know why Conditional
formatting was mentioned. The cell format (red or no color) in the range
B5:N145 are controlled by conditional formatting. So when the code looks
at
the cells that are red, they do not appear to be have any cell formatting
assigned.

Am I just out of luck?


"Bob Phillips" wrote in message
...
Range("B5").Interior.Colorindex = xlColorindexNone
For Each cell In Range("B6:N145")
If cell.Interior.Colorindex = 3 Then
Range("B5").Interior.Colorindex = 3
Exit For
End If
Next cell


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Patrick Simonds" wrote in message
...
I Tried this and it seems to work well using a
Worksheet_SelectionChange,
but I can not get cell B5 to return to a no fill color once the cells
in
the
Range B6:N145 are no longer red.

In other words when the condition which turns a cell red in the range
B6:N145 (it indicates a problem), corrected the red fill goes away and
I
then want the red fill in cell B5 to go away.


"Bob Phillips" wrote in message
...
Assuming it is not conditional formatting

For Each cell In Range("B6:N145")
If cell.Interior.Colorindex = 3 Then
Range("B5").Interior.Colorindex = 3
Exit For
End If
Next cell

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Patrick Simonds" wrote in message
...
I want to change B5 cell format to a red fill if any of the cells in
the
range B6-N145 has a cell fill format of red.