|
Cell Background Color Change according to numerical value
Conditional formatting will allow upto 3 conditions. I have five. How can you write an IF() statement/s to allow numerical values to change cell background color. 1-green, 2-blue, 3-yellow up to 5. Also how will you nest multiple IF's, the help files, aren't helping. -Joe -- jrd269 ------------------------------------------------------------------------ jrd269's Profile: http://www.excelforum.com/member.php...o&userid=23815 View this thread: http://www.excelforum.com/showthread...hreadid=374661 |
You can use conditional format with cell value between 3 and 5 -- anilsolipuram ------------------------------------------------------------------------ anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271 View this thread: http://www.excelforum.com/showthread...hreadid=374661 |
you can only add three conditions, after the third the 'add' button becomes greyed. -- jrd269 ------------------------------------------------------------------------ jrd269's Profile: http://www.excelforum.com/member.php...o&userid=23815 View this thread: http://www.excelforum.com/showthread...hreadid=374661 |
Yes conditional format has limitation of 3 conditions, you can use macro for this. I am codding the macro for conditional format for cell a2 but it can be applied to any number cells. you have to paste the below code, by right click sheet tab-view code and past the below code. test it by enter values 1,2,3,4,5,6 in cell a2 Private Sub Worksheet_Change(ByVal Target As Range) If (Target.Address = "$A$2") Then Range("a2").Select temp = Selection.Value If (temp = 1) Then Selection.Interior.ColorIndex = 4 ElseIf (temp = 2) Then Selection.Interior.ColorIndex = 33 ElseIf (temp = 3) Then Selection.Interior.ColorIndex = 36 ElseIf (temp = 4) Then Selection.Interior.ColorIndex = 48 ElseIf (temp = 5) Then Selection.Interior.ColorIndex = 38 ElseIf (temp = 6) Then Selection.Interior.ColorIndex = 30 End If End If End Sub -- anilsolipuram ------------------------------------------------------------------------ anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271 View this thread: http://www.excelforum.com/showthread...hreadid=374661 |
wow, thanks! I won't be able to try that until next week but thank you. you a life saver. -Joe -- jrd269 ------------------------------------------------------------------------ jrd269's Profile: http://www.excelforum.com/member.php...o&userid=23815 View this thread: http://www.excelforum.com/showthread...hreadid=374661 |
Worksheet functions can't change cell background colors (or font colors,
borders, etc.). If you need more than 3 conditions, you'll have to use VBA. If you search the archives: http://groups.google.com/advanced_gr...ugroup=*excel* you'll find myriad ways of accomplishing this, depending on your conditions. In article , jrd269 wrote: Conditional formatting will allow upto 3 conditions. I have five. How can you write an IF() statement/s to allow numerical values to change cell background color. 1-green, 2-blue, 3-yellow up to 5. Also how will you nest multiple IF's, the help files, aren't helping. |
from what the groups say, it looks like it cannot be done. which really stinks. I will continue to look, thanks for some direction -Joe -- jrd269 ------------------------------------------------------------------------ jrd269's Profile: http://www.excelforum.com/member.php...o&userid=23815 View this thread: http://www.excelforum.com/showthread...hreadid=374661 |
Didn't see the rest of your thread because you didn't stay in it but have
you tried a simple conditional format formatconditional format -- Don Guillett SalesAid Software "jrd269" wrote in message ... from what the groups say, it looks like it cannot be done. which really stinks. I will continue to look, thanks for some direction -Joe -- jrd269 ------------------------------------------------------------------------ jrd269's Profile: http://www.excelforum.com/member.php...o&userid=23815 View this thread: http://www.excelforum.com/showthread...hreadid=374661 |
yes i tried the simple conditional formatting, but only three conditions. Well 4 if you consider the default. But I found this add in, that says it can support up to 30 conditional formats found at: http://xldynamic.com/source/xld.CFPlus.Download.html might be worth checking out. I am going to get clearance then look into it. -Joe -- jrd269 ------------------------------------------------------------------------ jrd269's Profile: http://www.excelforum.com/member.php...o&userid=23815 View this thread: http://www.excelforum.com/showthread...hreadid=374661 |
That site works wonderfully. Dbl click the .xla file and install it, then you have to change a few marcos security settings and BAM it works. I setup 5 conditional formats with 5 different colors and it works. Great job to those people. Thank you lads. -Joe -- jrd269 ------------------------------------------------------------------------ jrd269's Profile: http://www.excelforum.com/member.php...o&userid=23815 View this thread: http://www.excelforum.com/showthread...hreadid=374661 |
I looked into the select case, but i am not a programmer. this was an easier way for me, the nonprogrammer, to do multiple conditions. -Joe -- jrd269 ------------------------------------------------------------------------ jrd269's Profile: http://www.excelforum.com/member.php...o&userid=23815 View this thread: http://www.excelforum.com/showthread...hreadid=374661 |
| All times are GMT +1. The time now is 12:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com