Change cell format based on condition.

[Patrick Simonds]

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.

[Norie]

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.

[Bob Phillips]

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" <ordnance1@comcast.net> wrote in message
news:OQ28UjyfFHA.3936@tk2msftngp13.phx.gbl...
> 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.
>
>

[Patrick Simonds]

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" <Norie.1rk3ea_1120331102.9141@excelforum-nospam.com> wrote in
message news:Norie.1rk3ea_1120331102.9141@excelforum-nospam.com...
>
> 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
>

[Patrick Simonds]

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" <ordnance1@comcast.net> wrote in message
news:Ov6jFj0fFHA.3936@TK2MSFTNGP10.phx.gbl...
>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" <Norie.1rk3ea_1120331102.9141@excelforum-nospam.com> wrote in
> message news:Norie.1rk3ea_1120331102.9141@excelforum-nospam.com...
>>
>> 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
>>
>
>

[Patrick Simonds]

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" <ordnance1@comcast.net> wrote in message
news:Ov6jFj0fFHA.3936@TK2MSFTNGP10.phx.gbl...
>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" <Norie.1rk3ea_1120331102.9141@excelforum-nospam.com> wrote in
> message news:Norie.1rk3ea_1120331102.9141@excelforum-nospam.com...
>>
>> 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
>>
>
>

[Patrick Simonds]

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" <bob.phillips@notheretiscali.co.uk> wrote in message
news:ODWronzfFHA.3448@TK2MSFTNGP12.phx.gbl...
> 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" <ordnance1@comcast.net> wrote in message
> news:OQ28UjyfFHA.3936@tk2msftngp13.phx.gbl...
>> 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.
>>
>>
>
>

[Bob Phillips]

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" <ordnance1@comcast.net> wrote in message
news:utJ0b80fFHA.2496@TK2MSFTNGP15.phx.gbl...
> 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" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:ODWronzfFHA.3448@TK2MSFTNGP12.phx.gbl...
> > 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" <ordnance1@comcast.net> wrote in message
> > news:OQ28UjyfFHA.3936@tk2msftngp13.phx.gbl...
> >> 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.
> >>
> >>
> >
> >
>
>

[Patrick Simonds]

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" <bob.phillips@notheretiscali.co.uk> wrote in message
news:O7hiYY1fFHA.2372@TK2MSFTNGP14.phx.gbl...
> 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" <ordnance1@comcast.net> wrote in message
> news:utJ0b80fFHA.2496@TK2MSFTNGP15.phx.gbl...
>> 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" <bob.phillips@notheretiscali.co.uk> wrote in message
>> news:ODWronzfFHA.3448@TK2MSFTNGP12.phx.gbl...
>> > 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" <ordnance1@comcast.net> wrote in message
>> > news:OQ28UjyfFHA.3936@tk2msftngp13.phx.gbl...
>> >> 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.
>> >>
>> >>
>> >
>> >
>>
>>
>
>

[Bob Phillips]

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" <ordnance1@comcast.net> wrote in message
news:u93cMo1fFHA.3944@TK2MSFTNGP10.phx.gbl...
> 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" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:O7hiYY1fFHA.2372@TK2MSFTNGP14.phx.gbl...
> > 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" <ordnance1@comcast.net> wrote in message
> > news:utJ0b80fFHA.2496@TK2MSFTNGP15.phx.gbl...
> >> 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" <bob.phillips@notheretiscali.co.uk> wrote in message
> >> news:ODWronzfFHA.3448@TK2MSFTNGP12.phx.gbl...
> >> > 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" <ordnance1@comcast.net> wrote in message
> >> > news:OQ28UjyfFHA.3936@tk2msftngp13.phx.gbl...
> >> >> 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.
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>