A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Incorporating Cell color fill in an "if" logical function?



 
 
Thread Tools Display Modes
  #11  
Old July 21st, 2008, 11:16 PM posted to microsoft.public.excel.worksheet.functions
Shawn
external usenet poster
 
Posts: 176
Default Incorporating Cell color fill in an "if" logical function?

Thanks for your help. Worked out perfect!!

"T. Valko" wrote:

I am using vista and excel 2007.


Try this...

Assume the cell in question is A1
Select cell A1
Goto Home tabStylesConditional FormattingManage Rules
Click New RuleUse a formula to determine.....
Enter this formula in the box: =A1="I"
Click the Format button
Select the desired style(s)
OKOKApply

Now, click New Rule and repeat the process for each of the conditions.

This was much easier in previous versions of Excel!


--
Biff
Microsoft Excel MVP


"Shawn" wrote in message
...
I have a similar question to this thread...

I have a cell that contains an if statement that returns either I, II, III
or IV depending on criteria. Can I also make the cell turn a different
colour
for each of these results? e.g. (Red for I, Orange for II, etc.) I used to
know how to do this in the old excel, but now I am using vista and excel
2007.

Thanks.

Shawn

"Miguel Zapico" wrote:

You can use Format-conditional formating, select "Formula is", and
insert
the condition that evaluates to true/false in the text box.

Hope this helps,
Miguel.

"George_Sky" wrote:

I want to identify a specific cell by highlighting it with a cell fill
color
when an "if" statement returns a "ture" response. How do I do this?




  #12  
Old July 22nd, 2008, 02:13 AM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default Incorporating Cell color fill in an "if" logical function?

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Shawn" wrote in message
...
Thanks for your help. Worked out perfect!!

"T. Valko" wrote:

I am using vista and excel 2007.


Try this...

Assume the cell in question is A1
Select cell A1
Goto Home tabStylesConditional FormattingManage Rules
Click New RuleUse a formula to determine.....
Enter this formula in the box: =A1="I"
Click the Format button
Select the desired style(s)
OKOKApply

Now, click New Rule and repeat the process for each of the conditions.

This was much easier in previous versions of Excel!


--
Biff
Microsoft Excel MVP


"Shawn" wrote in message
...
I have a similar question to this thread...

I have a cell that contains an if statement that returns either I, II,
III
or IV depending on criteria. Can I also make the cell turn a different
colour
for each of these results? e.g. (Red for I, Orange for II, etc.) I used
to
know how to do this in the old excel, but now I am using vista and
excel
2007.

Thanks.

Shawn

"Miguel Zapico" wrote:

You can use Format-conditional formating, select "Formula is", and
insert
the condition that evaluates to true/false in the text box.

Hope this helps,
Miguel.

"George_Sky" wrote:

I want to identify a specific cell by highlighting it with a cell
fill
color
when an "if" statement returns a "ture" response. How do I do this?






  #13  
Old October 7th, 2008, 06:01 PM posted to microsoft.public.excel.worksheet.functions
MT
external usenet poster
 
Posts: 41
Default Text Color

How do I go about formatting if I have more than 3 different conditions?
For example, text =
"Exceeds": Green
"Meets": Blue
"Below": Yellow
"Unacceptable": Red

thanks in advance for your help
  #14  
Old October 7th, 2008, 06:30 PM posted to microsoft.public.excel.worksheet.functions
Gord Dibben
external usenet poster
 
Posts: 20,252
Default Text Color

Which version of Excel?

If earlier than 2007 you would need some code or use Bob Phillips' CFPlus
add-in.

http://www.xldynamic.com/source/xld.....Download.html

Some event code...................

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A20" '=== change to suit
check_words = Array("Unacceptable", "Below", "Meets", "Exceeds")
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
For i = LBound(check_words) To UBound(check_words)
If InStr(1, .Value, check_words(i)) Then
Select Case i + 1
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
End Select
GoTo ws_exit
End If
Next i
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP



On Tue, 7 Oct 2008 10:01:03 -0700, MT wrote:

How do I go about formatting if I have more than 3 different conditions?
For example, text =
"Exceeds": Green
"Meets": Blue
"Below": Yellow
"Unacceptable": Red

thanks in advance for your help


  #15  
Old October 7th, 2008, 08:15 PM posted to microsoft.public.excel.worksheet.functions
MT
external usenet poster
 
Posts: 41
Default Text Color

Thanks Gord,

the add-in is helpful.
If i sent this same spreadsheet or opened it at another computer without the
add-in.
will the conditions still work? or on a different excel version.

Also,
i tried the event code: it's didn't work.
currently in using in excel 2003.

"Gord Dibben" wrote:

Which version of Excel?

If earlier than 2007 you would need some code or use Bob Phillips' CFPlus
add-in.

http://www.xldynamic.com/source/xld.....Download.html

Some event code...................

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A20" '=== change to suit
check_words = Array("Unacceptable", "Below", "Meets", "Exceeds")
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
For i = LBound(check_words) To UBound(check_words)
If InStr(1, .Value, check_words(i)) Then
Select Case i + 1
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
End Select
GoTo ws_exit
End If
Next i
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP



On Tue, 7 Oct 2008 10:01:03 -0700, MT wrote:

How do I go about formatting if I have more than 3 different conditions?
For example, text =
"Exceeds": Green
"Meets": Blue
"Below": Yellow
"Unacceptable": Red

thanks in advance for your help



  #16  
Old October 7th, 2008, 09:25 PM posted to microsoft.public.excel.worksheet.functions
Gord Dibben
external usenet poster
 
Posts: 20,252
Default Text Color

The recipient(s) would require CFPlus add-in to be accessible.

What is "didn't work" with regards to the event code?

The code needs to be pasted into the sheet module.

Right-click on the sheet tab and "View Code".

Paste the code to that module.


Gord

On Tue, 7 Oct 2008 12:15:01 -0700, MT wrote:

Thanks Gord,

the add-in is helpful.
If i sent this same spreadsheet or opened it at another computer without the
add-in.
will the conditions still work? or on a different excel version.

Also,
i tried the event code: it's didn't work.
currently in using in excel 2003.

"Gord Dibben" wrote:

Which version of Excel?

If earlier than 2007 you would need some code or use Bob Phillips' CFPlus
add-in.

http://www.xldynamic.com/source/xld.....Download.html

Some event code...................

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A20" '=== change to suit
check_words = Array("Unacceptable", "Below", "Meets", "Exceeds")
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
For i = LBound(check_words) To UBound(check_words)
If InStr(1, .Value, check_words(i)) Then
Select Case i + 1
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
End Select
GoTo ws_exit
End If
Next i
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP



On Tue, 7 Oct 2008 10:01:03 -0700, MT wrote:

How do I go about formatting if I have more than 3 different conditions?
For example, text =
"Exceeds": Green
"Meets": Blue
"Below": Yellow
"Unacceptable": Red

thanks in advance for your help




  #17  
Old December 9th, 2008, 06:16 PM posted to microsoft.public.excel.worksheet.functions
FMC
external usenet poster
 
Posts: 8
Default Incorporating Cell color fill in an "if" logical function?

Hello
Do you know how to do this in 2007 excel?

Thanks!

"Spiky" wrote:

On Jun 20, 6:11 pm, John wrote:
To be honest, I found both the question and the response to be very confusing.


Well, you dredged up a 2 year old thread for an offhand comment.
That's a little confusing, also.

But I'll assume you want to know about this. Maybe you should go open
Conditional Formatting to see what they were talking about. It won't
make sense if you don't look at it. It's in the Format menu in Excel.
Miguel's original response was right on, although Bernard's was more
detailed.

  #18  
Old January 2nd, 2009, 04:41 AM posted to microsoft.public.excel.worksheet.functions
Peace153
external usenet poster
 
Posts: 2
Default Incorporating Cell color fill in an "if" logical function?



"T. Valko" wrote:

I am using vista and excel 2007.


Try this...

Assume the cell in question is A1
Select cell A1
Goto Home tabStylesConditional FormattingManage Rules
Click New RuleUse a formula to determine.....
Enter this formula in the box: =A1="I"
Click the Format button
Select the desired style(s)
OKOKApply

Now, click New Rule and repeat the process for each of the conditions.

This was much easier in previous versions of Excel!


--
Biff
Microsoft Excel MVP


"Shawn" wrote in message
...
I have a similar question to this thread...

I have a cell that contains an if statement that returns either I, II, III
or IV depending on criteria. Can I also make the cell turn a different
colour
for each of these results? e.g. (Red for I, Orange for II, etc.) I used to
know how to do this in the old excel, but now I am using vista and excel
2007.

Thanks.

Shawn

"Miguel Zapico" wrote:

You can use Format-conditional formating, select "Formula is", and
insert
the condition that evaluates to true/false in the text box.

Hope this helps,
Miguel.

"George_Sky" wrote:

I want to identify a specific cell by highlighting it with a cell fill
color
when an "if" statement returns a "ture" response. How do I do this?




  #19  
Old January 2nd, 2009, 04:42 AM posted to microsoft.public.excel.worksheet.functions
Peace153
external usenet poster
 
Posts: 2
Default Incorporating Cell color fill in an "if" logical function?

Thanks for this tip. It worked for me too.

"T. Valko" wrote:

I am using vista and excel 2007.


Try this...

Assume the cell in question is A1
Select cell A1
Goto Home tabStylesConditional FormattingManage Rules
Click New RuleUse a formula to determine.....
Enter this formula in the box: =A1="I"
Click the Format button
Select the desired style(s)
OKOKApply

Now, click New Rule and repeat the process for each of the conditions.

This was much easier in previous versions of Excel!


--
Biff
Microsoft Excel MVP


"Shawn" wrote in message
...
I have a similar question to this thread...

I have a cell that contains an if statement that returns either I, II, III
or IV depending on criteria. Can I also make the cell turn a different
colour
for each of these results? e.g. (Red for I, Orange for II, etc.) I used to
know how to do this in the old excel, but now I am using vista and excel
2007.

Thanks.

Shawn

"Miguel Zapico" wrote:

You can use Format-conditional formating, select "Formula is", and
insert
the condition that evaluates to true/false in the text box.

Hope this helps,
Miguel.

"George_Sky" wrote:

I want to identify a specific cell by highlighting it with a cell fill
color
when an "if" statement returns a "ture" response. How do I do this?




  #20  
Old January 2nd, 2009, 07:20 AM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default Incorporating Cell color fill in an "if" logical function?

Glad to help. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Peace153" wrote in message
...
Thanks for this tip. It worked for me too.

"T. Valko" wrote:

I am using vista and excel 2007.


Try this...

Assume the cell in question is A1
Select cell A1
Goto Home tabStylesConditional FormattingManage Rules
Click New RuleUse a formula to determine.....
Enter this formula in the box: =A1="I"
Click the Format button
Select the desired style(s)
OKOKApply

Now, click New Rule and repeat the process for each of the conditions.

This was much easier in previous versions of Excel!


--
Biff
Microsoft Excel MVP


"Shawn" wrote in message
...
I have a similar question to this thread...

I have a cell that contains an if statement that returns either I, II,
III
or IV depending on criteria. Can I also make the cell turn a different
colour
for each of these results? e.g. (Red for I, Orange for II, etc.) I used
to
know how to do this in the old excel, but now I am using vista and
excel
2007.

Thanks.

Shawn

"Miguel Zapico" wrote:

You can use Format-conditional formating, select "Formula is", and
insert
the condition that evaluates to true/false in the text box.

Hope this helps,
Miguel.

"George_Sky" wrote:

I want to identify a specific cell by highlighting it with a cell
fill
color
when an "if" statement returns a "ture" response. How do I do this?






 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Urgent date/scheduling calc needed jct Worksheet Functions 3 February 24th, 2006 02:36 AM
Function syntax to compare cell contents ES Worksheet Functions 2 May 18th, 2005 03:53 PM
IF E3 & E10 = TRUE set this cell to "Yes", else set to "No" Timothy L Worksheet Functions 5 August 27th, 2004 02:28 AM
If/And function help Milehigh Worksheet Functions 5 January 30th, 2004 05:15 PM
Convert a Cell Reference to Text Chuck Buker Worksheet Functions 6 September 22nd, 2003 05:04 PM


All times are GMT +1. The time now is 04:39 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.