Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 2nd 06, 05:30 PM posted to microsoft.public.excel.worksheet.functions
MMM
 
Posts: n/a
Default Conditional Formatting for more than 3 conditions

Hi,
I tried downloading from the xldynamic site, but I think I am doing
something wrong. I get a zip file, and when I extract files I do not get a
..ini file only a .xls file and nothing happens after that.

Can someone help me write a Macro for this? It's simple:

I have to look down a range of columns and rows (Say A1: H30) and, based on
the text there, which could be one of the following letters:
1) G
2) R
3) N
4) Y
5) C
6) W

I need a different color for each type of cell: 1) G=Green with dark green
font for the letter, 2) R=Red with red background and black for letter R, 3)
N= Purple background and Yellow font, 4) Y=Yellow background, black font, 5)
C=Blue background dark blue font 6) W= White background, blue font,

I have no clue how to write a Macro, your help will be highly appreciated,
thanks


--
MMM

  #2   Report Post  
Old February 2nd 06, 06:02 PM posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default Conditional Formatting for more than 3 conditions

It shouldn;t be an xls file but an xla file.

Here is a VBA example that sets the cell colour


Private Sub Worksheet_Change(ByVal Target As Range)


On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:H30")) Is Nothing Then
With Target
Select Case UCase(.Value)
Case "B": .Interior.ColorIndex = 5
Case "O": .Interior.ColorIndex = 46
Case "P": .Interior.ColorIndex = 7
Case "R": .Interior.ColorIndex = 3
'etc.
End Select


End With
End If


ws_exit:
Application.EnableEvents = True
End Sub


'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"MMM" wrote in message
...
Hi,
I tried downloading from the xldynamic site, but I think I am doing
something wrong. I get a zip file, and when I extract files I do not get a
.ini file only a .xls file and nothing happens after that.

Can someone help me write a Macro for this? It's simple:

I have to look down a range of columns and rows (Say A1: H30) and, based

on
the text there, which could be one of the following letters:
1) G
2) R
3) N
4) Y
5) C
6) W

I need a different color for each type of cell: 1) G=Green with dark green
font for the letter, 2) R=Red with red background and black for letter R,

3)
N= Purple background and Yellow font, 4) Y=Yellow background, black font,

5)
C=Blue background dark blue font 6) W= White background, blue font,

I have no clue how to write a Macro, your help will be highly appreciated,
thanks


--
MMM



  #3   Report Post  
Old February 2nd 06, 09:54 PM posted to microsoft.public.excel.worksheet.functions
Chip Pearson
 
Posts: n/a
Default Conditional Formatting for more than 3 conditions

Note that Bob's code will run only when the cell's value is
changed by the user, not, as is the case with Conditional
Formatting, when the change is the result of a calculation.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Bob Phillips" wrote in
message ...
It shouldn;t be an xls file but an xla file.

Here is a VBA example that sets the cell colour


Private Sub Worksheet_Change(ByVal Target As Range)


On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:H30")) Is Nothing Then
With Target
Select Case UCase(.Value)
Case "B": .Interior.ColorIndex = 5
Case "O": .Interior.ColorIndex = 46
Case "P": .Interior.ColorIndex = 7
Case "R": .Interior.ColorIndex = 3
'etc.
End Select


End With
End If


ws_exit:
Application.EnableEvents = True
End Sub


'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a
standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"MMM" wrote in message
...
Hi,
I tried downloading from the xldynamic site, but I think I am
doing
something wrong. I get a zip file, and when I extract files I
do not get a
.ini file only a .xls file and nothing happens after that.

Can someone help me write a Macro for this? It's simple:

I have to look down a range of columns and rows (Say A1: H30)
and, based

on
the text there, which could be one of the following letters:
1) G
2) R
3) N
4) Y
5) C
6) W

I need a different color for each type of cell: 1) G=Green
with dark green
font for the letter, 2) R=Red with red background and black
for letter R,

3)
N= Purple background and Yellow font, 4) Y=Yellow background,
black font,

5)
C=Blue background dark blue font 6) W= White background, blue
font,

I have no clue how to write a Macro, your help will be highly
appreciated,
thanks


--
MMM





  #4   Report Post  
Old February 3rd 06, 05:14 PM posted to microsoft.public.excel.worksheet.functions
MMM
 
Posts: n/a
Default Conditional Formatting for more than 3 conditions

Bob,

Your add-in is absolutely awesome once I got it to work. Kudos!
--
MMM


"Bob Phillips" wrote:

It shouldn;t be an xls file but an xla file.

Here is a VBA example that sets the cell colour


Private Sub Worksheet_Change(ByVal Target As Range)


On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:H30")) Is Nothing Then
With Target
Select Case UCase(.Value)
Case "B": .Interior.ColorIndex = 5
Case "O": .Interior.ColorIndex = 46
Case "P": .Interior.ColorIndex = 7
Case "R": .Interior.ColorIndex = 3
'etc.
End Select


End With
End If


ws_exit:
Application.EnableEvents = True
End Sub


'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"MMM" wrote in message
...
Hi,
I tried downloading from the xldynamic site, but I think I am doing
something wrong. I get a zip file, and when I extract files I do not get a
.ini file only a .xls file and nothing happens after that.

Can someone help me write a Macro for this? It's simple:

I have to look down a range of columns and rows (Say A1: H30) and, based

on
the text there, which could be one of the following letters:
1) G
2) R
3) N
4) Y
5) C
6) W

I need a different color for each type of cell: 1) G=Green with dark green
font for the letter, 2) R=Red with red background and black for letter R,

3)
N= Purple background and Yellow font, 4) Y=Yellow background, black font,

5)
C=Blue background dark blue font 6) W= White background, blue font,

I have no clue how to write a Macro, your help will be highly appreciated,
thanks


--
MMM




  #5   Report Post  
Old March 9th 06, 01:43 AM posted to microsoft.public.excel.worksheet.functions
bkcoughlin
 
Posts: n/a
Default Conditional Formatting for more than 3 conditions


I'm trying to do exactly that--make it change cell color based on the
calculated value, and have more than 3 conditions. Is there a
modification to this that someone can suggest?

Thanks in advance for any insight anyone can offer.

Brian


Chip Pearson Wrote:
Note that Bob's code will run only when the cell's value is
changed by the user, not, as is the case with Conditional
Formatting, when the change is the result of a calculation.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



--
bkcoughlin
------------------------------------------------------------------------
bkcoughlin's Profile: http://www.excelforum.com/member.php...o&userid=32284
View this thread: http://www.excelforum.com/showthread...hreadid=507761



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
More than 3 Conditional Formatting Conditions Beth H Excel Worksheet Functions 12 January 6th 06 07:35 PM
Conditional formatting capability for 20+ conditions not just 3. Charles Smittenaar Excel Discussion (Misc queries) 2 October 28th 05 11:30 PM
Additional Conditions for Conditional Formatting eric beck Excel Worksheet Functions 1 July 25th 05 06:47 AM
Conditional formatting: I have five conditions, how to do this? Danzguy Excel Discussion (Misc queries) 2 May 11th 05 08:21 AM
Adding more than three Conditions to 'Conditional Formatting' David McRitchie Excel Discussion (Misc queries) 1 November 27th 04 06:03 PM


All times are GMT +1. The time now is 06:36 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017