| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match Function(Code modification)
Hey guys,
If Application.CountIf(.Range("IV1:IV100"), "Add Stat Name Here") 0 Then Range("area22").Interior.ColorIndex = Range("IV1").Interior.ColorIndex Else Range("area22").Interior.ColorIndex = 2 End If Above is a working code that will color a Named Range of cells a specified color if the value "Productivity" is found in Range ("IV1:IV100"). In this example the named range is "area22" and the specified color is associated with the variable "Range("IV1").Interior.ColorIndex". Also in this example the Value "Productivity is located in cell A4. If the value "Productivity" is not found in Range ("IV1:IV100"), then Range "area22" will be shaded color 2. In the above example, in order for the code to work, you must specify what value to look for in Range ("IV1:IV100"). Instead, what I would like is for the code to look in Range ("IV1:IV100") and if it finds the value of cell A4, then color Range "area22" the variable "Range("IV1").Interior.ColorIndex". If it does not find the value, then color Range "area22" color 2. Thank you Todd Huttenstine |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Match Function(Code modification)
Todd, try this
If Application.CountIf(Range("IV1:IV100"), Range("A4")) 0 Then Range("area22").Interior.ColorIndex = Range("B1").Interior.ColorIndex Else Range("area22").Interior.ColorIndex = 2 End If Cheers N "Todd Huttenstine(Remote)" wrote in message ... Hey guys, If Application.CountIf(.Range("IV1:IV100"), "Add Stat Name Here") 0 Then Range("area22").Interior.ColorIndex = Range("IV1").Interior.ColorIndex Else Range("area22").Interior.ColorIndex = 2 End If Above is a working code that will color a Named Range of cells a specified color if the value "Productivity" is found in Range ("IV1:IV100"). In this example the named range is "area22" and the specified color is associated with the variable "Range("IV1").Interior.ColorIndex". Also in this example the Value "Productivity is located in cell A4. If the value "Productivity" is not found in Range ("IV1:IV100"), then Range "area22" will be shaded color 2. In the above example, in order for the code to work, you must specify what value to look for in Range ("IV1:IV100"). Instead, what I would like is for the code to look in Range ("IV1:IV100") and if it finds the value of cell A4, then color Range "area22" the variable "Range("IV1").Interior.ColorIndex". If it does not find the value, then color Range "area22" color 2. Thank you Todd Huttenstine ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Match Function(Code modification)
correction in line 2 to read "IV1" not "B1"
If Application.CountIf(Range("IV1:IV100"), Range("A4")) 0 Then Range("area22").Interior.ColorIndex = Range("IV1").Interior.ColorIndex Else Range("area22").Interior.ColorIndex = 2 End If "Nigel" wrote in message ... Todd, try this If Application.CountIf(Range("IV1:IV100"), Range("A4")) 0 Then Range("area22").Interior.ColorIndex = Range("B1").Interior.ColorIndex Else Range("area22").Interior.ColorIndex = 2 End If Cheers N "Todd Huttenstine(Remote)" wrote in message ... Hey guys, If Application.CountIf(.Range("IV1:IV100"), "Add Stat Name Here") 0 Then Range("area22").Interior.ColorIndex = Range("IV1").Interior.ColorIndex Else Range("area22").Interior.ColorIndex = 2 End If Above is a working code that will color a Named Range of cells a specified color if the value "Productivity" is found in Range ("IV1:IV100"). In this example the named range is "area22" and the specified color is associated with the variable "Range("IV1").Interior.ColorIndex". Also in this example the Value "Productivity is located in cell A4. If the value "Productivity" is not found in Range ("IV1:IV100"), then Range "area22" will be shaded color 2. In the above example, in order for the code to work, you must specify what value to look for in Range ("IV1:IV100"). Instead, what I would like is for the code to look in Range ("IV1:IV100") and if it finds the value of cell A4, then color Range "area22" the variable "Range("IV1").Interior.ColorIndex". If it does not find the value, then color Range "area22" color 2. Thank you Todd Huttenstine ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Match Function(Code modification)
thanx
it worked. "Nigel" wrote in message ... correction in line 2 to read "IV1" not "B1" If Application.CountIf(Range("IV1:IV100"), Range("A4")) 0 Then Range("area22").Interior.ColorIndex = Range("IV1").Interior.ColorIndex Else Range("area22").Interior.ColorIndex = 2 End If "Nigel" wrote in message ... Todd, try this If Application.CountIf(Range("IV1:IV100"), Range("A4")) 0 Then Range("area22").Interior.ColorIndex = Range("B1").Interior.ColorIndex Else Range("area22").Interior.ColorIndex = 2 End If Cheers N "Todd Huttenstine(Remote)" wrote in message ... Hey guys, If Application.CountIf(.Range("IV1:IV100"), "Add Stat Name Here") 0 Then Range("area22").Interior.ColorIndex = Range("IV1").Interior.ColorIndex Else Range("area22").Interior.ColorIndex = 2 End If Above is a working code that will color a Named Range of cells a specified color if the value "Productivity" is found in Range ("IV1:IV100"). In this example the named range is "area22" and the specified color is associated with the variable "Range("IV1").Interior.ColorIndex". Also in this example the Value "Productivity is located in cell A4. If the value "Productivity" is not found in Range ("IV1:IV100"), then Range "area22" will be shaded color 2. In the above example, in order for the code to work, you must specify what value to look for in Range ("IV1:IV100"). Instead, what I would like is for the code to look in Range ("IV1:IV100") and if it finds the value of cell A4, then color Range "area22" the variable "Range("IV1").Interior.ColorIndex". If it does not find the value, then color Range "area22" color 2. Thank you Todd Huttenstine ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| modification to this code | Excel Discussion (Misc queries) | |||
| Code modification help | Excel Worksheet Functions | |||
| line code modification | Excel Discussion (Misc queries) | |||
| Sort Code Modification | Excel Programming | |||
| Modification to code | Excel Programming | |||
Linear Mode
