Is there a way to do basically a reverse of the conditional formatting -
where the certain cells have a pattern assigned to them already, and is there
a way to in another column have that change to either a numeric value or even
a true false.
Like if cell A1 has a red background have B1 come up with sometype of a
number that would be different that if the background was blank.
Why not use the value that has caused the other cell to come up red.
So if your conditional formatting for A1 depends on say the content
being greater than 10, then using an if statement in B1 to insert the
value you want =if(A1gt;10,Value you want).
Ed--
EdMac
------------------------------------------------------------------------
EdMac's Profile: www.excelforum.com/member.php...oamp;userid=30736
View this thread: www.excelforum.com/showthread...hreadid=512291Because there are not values that caused the coloring - that was entered by
hand
quot;EdMacquot; wrote:
gt;
gt; Why not use the value that has caused the other cell to come up red.
gt;
gt; So if your conditional formatting for A1 depends on say the content
gt; being greater than 10, then using an if statement in B1 to insert the
gt; value you want =if(A1gt;10,Value you want).
gt;
gt; Ed
gt;
gt;
gt; --
gt; EdMac
gt; ------------------------------------------------------------------------
gt; EdMac's Profile: www.excelforum.com/member.php...oamp;userid=30736
gt; View this thread: www.excelforum.com/showthread...hreadid=512291
gt;
gt;
Then it isn't conditional formatting.
You will need a function, like this
Function CI(rng as Range)
If rng.Count gt; 1 Then
CI = cvErr(xlErrRef)
Else
Ci = rng.Interior.Colorindex
End If
End Function
and in your cell, use
=IF(CI(A1)=3,99,100)
which tests for Red.
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
quot;Steve Curtisquot; gt; wrote in message
...
gt; Because there are not values that caused the coloring - that was entered
by
gt; hand
gt;
gt; quot;EdMacquot; wrote:
gt;
gt; gt;
gt; gt; Why not use the value that has caused the other cell to come up red.
gt; gt;
gt; gt; So if your conditional formatting for A1 depends on say the content
gt; gt; being greater than 10, then using an if statement in B1 to insert the
gt; gt; value you want =if(A1gt;10,Value you want).
gt; gt;
gt; gt; Ed
gt; gt;
gt; gt;
gt; gt; --
gt; gt; EdMac
gt; gt; ------------------------------------------------------------------------
gt; gt; EdMac's Profile:
www.excelforum.com/member.php...oamp;userid=30736
gt; gt; View this thread:
www.excelforum.com/showthread...hreadid=512291
gt; gt;
gt; gt;
Thanks Bob, that works great - but here is another question - I know that the
3 is for Red, how do I find out what the other related numbers are for other
colors?quot;Bob Phillipsquot; wrote:
gt; Then it isn't conditional formatting.
gt;
gt; You will need a function, like this
gt;
gt; Function CI(rng as Range)
gt; If rng.Count gt; 1 Then
gt; CI = cvErr(xlErrRef)
gt; Else
gt; Ci = rng.Interior.Colorindex
gt; End If
gt; End Function
gt;
gt; and in your cell, use
gt;
gt; =IF(CI(A1)=3,99,100)
gt;
gt; which tests for Red.
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Steve Curtisquot; gt; wrote in message
gt; ...
gt; gt; Because there are not values that caused the coloring - that was entered
gt; by
gt; gt; hand
gt; gt;
gt; gt; quot;EdMacquot; wrote:
gt; gt;
gt; gt; gt;
gt; gt; gt; Why not use the value that has caused the other cell to come up red.
gt; gt; gt;
gt; gt; gt; So if your conditional formatting for A1 depends on say the content
gt; gt; gt; being greater than 10, then using an if statement in B1 to insert the
gt; gt; gt; value you want =if(A1gt;10,Value you want).
gt; gt; gt;
gt; gt; gt; Ed
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; EdMac
gt; gt; gt; ------------------------------------------------------------------------
gt; gt; gt; EdMac's Profile:
gt; www.excelforum.com/member.php...oamp;userid=30736
gt; gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=512291
gt; gt; gt;
gt; gt; gt;
gt;
gt;
gt;
Steve,
See this previous post from myself and Peter T, most are given there
tinyurl.com/annkb
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
quot;Steve Curtisquot; gt; wrote in message
...
gt; Thanks Bob, that works great - but here is another question - I know that
the
gt; 3 is for Red, how do I find out what the other related numbers are for
other
gt; colors?
gt;
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; Then it isn't conditional formatting.
gt; gt;
gt; gt; You will need a function, like this
gt; gt;
gt; gt; Function CI(rng as Range)
gt; gt; If rng.Count gt; 1 Then
gt; gt; CI = cvErr(xlErrRef)
gt; gt; Else
gt; gt; Ci = rng.Interior.Colorindex
gt; gt; End If
gt; gt; End Function
gt; gt;
gt; gt; and in your cell, use
gt; gt;
gt; gt; =IF(CI(A1)=3,99,100)
gt; gt;
gt; gt; which tests for Red.
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;Steve Curtisquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; Because there are not values that caused the coloring - that was
entered
gt; gt; by
gt; gt; gt; hand
gt; gt; gt;
gt; gt; gt; quot;EdMacquot; wrote:
gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; Why not use the value that has caused the other cell to come up red.
gt; gt; gt; gt;
gt; gt; gt; gt; So if your conditional formatting for A1 depends on say the content
gt; gt; gt; gt; being greater than 10, then using an if statement in B1 to insert
the
gt; gt; gt; gt; value you want =if(A1gt;10,Value you want).
gt; gt; gt; gt;
gt; gt; gt; gt; Ed
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt; EdMac
gt; gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; gt; gt; EdMac's Profile:
gt; gt; www.excelforum.com/member.php...oamp;userid=30736
gt; gt; gt; gt; View this thread:
gt; gt; www.excelforum.com/showthread...hreadid=512291
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;
Thanks Bob, the other question I had, is how do you get this to update those
equations or to re-calculate when you change the colors of the cells? I did
check to make sure that my recalc is set to automatic already.
quot;Bob Phillipsquot; wrote:
gt; Steve,
gt;
gt; See this previous post from myself and Peter T, most are given there
gt; tinyurl.com/annkb
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Steve Curtisquot; gt; wrote in message
gt; ...
gt; gt; Thanks Bob, that works great - but here is another question - I know that
gt; the
gt; gt; 3 is for Red, how do I find out what the other related numbers are for
gt; other
gt; gt; colors?
gt; gt;
gt; gt;
gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt;
gt; gt; gt; Then it isn't conditional formatting.
gt; gt; gt;
gt; gt; gt; You will need a function, like this
gt; gt; gt;
gt; gt; gt; Function CI(rng as Range)
gt; gt; gt; If rng.Count gt; 1 Then
gt; gt; gt; CI = cvErr(xlErrRef)
gt; gt; gt; Else
gt; gt; gt; Ci = rng.Interior.Colorindex
gt; gt; gt; End If
gt; gt; gt; End Function
gt; gt; gt;
gt; gt; gt; and in your cell, use
gt; gt; gt;
gt; gt; gt; =IF(CI(A1)=3,99,100)
gt; gt; gt;
gt; gt; gt; which tests for Red.
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; HTH
gt; gt; gt;
gt; gt; gt; Bob Phillips
gt; gt; gt;
gt; gt; gt; (remove nothere from email address if mailing direct)
gt; gt; gt;
gt; gt; gt; quot;Steve Curtisquot; gt; wrote in message
gt; gt; gt; ...
gt; gt; gt; gt; Because there are not values that caused the coloring - that was
gt; entered
gt; gt; gt; by
gt; gt; gt; gt; hand
gt; gt; gt; gt;
gt; gt; gt; gt; quot;EdMacquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Why not use the value that has caused the other cell to come up red.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; So if your conditional formatting for A1 depends on say the content
gt; gt; gt; gt; gt; being greater than 10, then using an if statement in B1 to insert
gt; the
gt; gt; gt; gt; gt; value you want =if(A1gt;10,Value you want).
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Ed
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; EdMac
gt; gt; gt; gt;
gt; gt; ------------------------------------------------------------------------
gt; gt; gt; gt; gt; EdMac's Profile:
gt; gt; gt; www.excelforum.com/member.php...oamp;userid=30736
gt; gt; gt; gt; gt; View this thread:
gt; gt; gt; www.excelforum.com/showthread...hreadid=512291
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;
gt;
gt;
There is the big problem. Unfortunately, changing a cell colour does not
trigger any event, so there is no automated way. The best we can do is to
add this to the start of the function
Application.Volatile
and then when you change a colour, hit the F9 key.
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
quot;Steve Curtisquot; gt; wrote in message
...
gt; Thanks Bob, the other question I had, is how do you get this to update
those
gt; equations or to re-calculate when you change the colors of the cells? I
did
gt; check to make sure that my recalc is set to automatic already.
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; Steve,
gt; gt;
gt; gt; See this previous post from myself and Peter T, most are given there
gt; gt; tinyurl.com/annkb
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;Steve Curtisquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; Thanks Bob, that works great - but here is another question - I know
that
gt; gt; the
gt; gt; gt; 3 is for Red, how do I find out what the other related numbers are for
gt; gt; other
gt; gt; gt; colors?
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Then it isn't conditional formatting.
gt; gt; gt; gt;
gt; gt; gt; gt; You will need a function, like this
gt; gt; gt; gt;
gt; gt; gt; gt; Function CI(rng as Range)
gt; gt; gt; gt; If rng.Count gt; 1 Then
gt; gt; gt; gt; CI = cvErr(xlErrRef)
gt; gt; gt; gt; Else
gt; gt; gt; gt; Ci = rng.Interior.Colorindex
gt; gt; gt; gt; End If
gt; gt; gt; gt; End Function
gt; gt; gt; gt;
gt; gt; gt; gt; and in your cell, use
gt; gt; gt; gt;
gt; gt; gt; gt; =IF(CI(A1)=3,99,100)
gt; gt; gt; gt;
gt; gt; gt; gt; which tests for Red.
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt; HTH
gt; gt; gt; gt;
gt; gt; gt; gt; Bob Phillips
gt; gt; gt; gt;
gt; gt; gt; gt; (remove nothere from email address if mailing direct)
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Steve Curtisquot; gt; wrote in
message
gt; gt; gt; gt; ...
gt; gt; gt; gt; gt; Because there are not values that caused the coloring - that was
gt; gt; entered
gt; gt; gt; gt; by
gt; gt; gt; gt; gt; hand
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;EdMacquot; wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Why not use the value that has caused the other cell to come up
red.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; So if your conditional formatting for A1 depends on say the
content
gt; gt; gt; gt; gt; gt; being greater than 10, then using an if statement in B1 to
insert
gt; gt; the
gt; gt; gt; gt; gt; gt; value you want =if(A1gt;10,Value you want).
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Ed
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; gt; EdMac
gt; gt; gt; gt; gt;
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; gt; gt; gt; gt; EdMac's Profile:
gt; gt; gt; gt; www.excelforum.com/member.php...oamp;userid=30736
gt; gt; gt; gt; gt; gt; View this thread:
gt; gt; gt; gt; www.excelforum.com/showthread...hreadid=512291
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;
留言列表
