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 » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Conditional Formatting



 
 
Thread Tools Display Modes
  #1  
Old May 18th, 2010, 11:25 PM posted to microsoft.public.excel.newusers
John Calder
external usenet poster
 
Posts: 192
Default Conditional Formatting

Hi

I run Excel 2K

Conditional Formatting in Excel 2K only allows three conditions to be
applied. I need 8 conditions.

What I have is 8 check boxes that indictate TRUE or FALSE depending on
wheather the check box had been ticked or not.

Check Box 1 linked to cell IN2
Check Box 2 linked to cell IO2
Check Box 3 linked to cell IP2
Check Box 4 linked to cell IQ2
Check Box 5 linked to cell IR2
Check Box 6 linked to cell IS2
Check Box 7 linked to cell IT2
Check Box 8 linked to cell IU2

I would like to nominate a cell (say U2) that changes colour depending on
which of the 8 check boxes is ticked.

Is there a way of doing this?

Thanks
Ads
  #2  
Old May 19th, 2010, 12:30 AM posted to microsoft.public.excel.newusers
Max
external usenet poster
 
Posts: 8,574
Default Conditional Formatting

I'd use a simpler way to just return the selection made as a value
Assume your array of 8 contiguous horiz cells is in A1:H1
where there will only be a single TRUE at any time
In say, A2:
=IF(ISNA(MATCH(TRUE,A1:H1,0)),"",MATCH(TRUE,A1:H1, 0))
will return a relative number denoting which cell within A1:H1 contains the
TRUE, eg: 2 = B1, 5 = E1, and so on. Any worth? hit the YES below
--
Max
Singapore
---
"John Calder" wrote:
I run Excel 2K

Conditional Formatting in Excel 2K only allows three conditions to be
applied. I need 8 conditions.

What I have is 8 check boxes that indictate TRUE or FALSE depending on
wheather the check box had been ticked or not.

Check Box 1 linked to cell IN2
Check Box 2 linked to cell IO2
Check Box 3 linked to cell IP2
Check Box 4 linked to cell IQ2
Check Box 5 linked to cell IR2
Check Box 6 linked to cell IS2
Check Box 7 linked to cell IT2
Check Box 8 linked to cell IU2

I would like to nominate a cell (say U2) that changes colour depending on
which of the 8 check boxes is ticked.

Is there a way of doing this?

Thanks

  #3  
Old May 19th, 2010, 12:43 AM posted to microsoft.public.excel.newusers
John Calder
external usenet poster
 
Posts: 192
Default Conditional Formatting

Max

The person I am doing this for is really keen to have colour as the
identifier but if all else fails I will definately look at your option.


Thanks

"John Calder" wrote:

Hi

I run Excel 2K

Conditional Formatting in Excel 2K only allows three conditions to be
applied. I need 8 conditions.

What I have is 8 check boxes that indictate TRUE or FALSE depending on
wheather the check box had been ticked or not.

Check Box 1 linked to cell IN2
Check Box 2 linked to cell IO2
Check Box 3 linked to cell IP2
Check Box 4 linked to cell IQ2
Check Box 5 linked to cell IR2
Check Box 6 linked to cell IS2
Check Box 7 linked to cell IT2
Check Box 8 linked to cell IU2

I would like to nominate a cell (say U2) that changes colour depending on
which of the 8 check boxes is ticked.

Is there a way of doing this?

Thanks

  #4  
Old May 19th, 2010, 02:56 AM posted to microsoft.public.excel.newusers
Max
external usenet poster
 
Posts: 8,574
Default Conditional Formatting

With cell A2 housing the earlier formula returning the numbers 1 - 8, think
you could try tinkering with the sheet sub below to fill-color A2, with the
font color for A2 set to match the fill color so as to mask the underlying
number returned by the formula

To install:
Copy the sub
Right-click on the sheet tab View Code
Paste the sub into the code window on the right
Press Alt + Q to get back to Excel

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer, fcolor As Integer
Set Target = [A2]
Select Case Target
Case 1
icolor = 6
fcolor = 6
Case 2
icolor = 12
fcolor = 12
Case 3
icolor = 7
fcolor = 7
Case 4
icolor = 53
fcolor = 53
Case 5
icolor = 15
fcolor = 15
Case 6
icolor = 42
fcolor = 42
Case 7
icolor = 50
fcolor = 50
Case 8
icolor = 13
fcolor = 13
Case Else
End Select

With Target
.Interior.ColorIndex = icolor
.Font.ColorIndex = fcolor
End With

End Sub

--
Max
Singapore
---
"John Calder" wrote:
Max

The person I am doing this for is really keen to have colour as the
identifier but if all else fails I will definately look at your option

  #5  
Old May 20th, 2010, 12:49 AM posted to microsoft.public.excel.newusers
John Calder
external usenet poster
 
Posts: 192
Default Conditional Formatting

Max

Thanks again for your help. I have no problem getting the 1st part to work
but the VB does not work for me. At best I got it to change to one colour
(yellow) but it would not change to any other colour.

The cells which I need to colour have a $ value input.

Example

U2 = $150
U3 = $300
U3 = $0
U4 = $49

etc etc

These are the cells I need to change colour. (A different colour represents
a different person)


The cells which contain your folrmula are

IM2 =IF(ISNA(MATCH(TRUE,IN2:IU2,0)),"",MATCH(TRUE,IN2: IU2,0))
IM3 =IF(ISNA(MATCH(TRUE,IN3:IU3,0)),"",MATCH(TRUE,IN3: IU3,0))
IM4 =IF(ISNA(MATCH(TRUE,IN4:IU4,0)),"",MATCH(TRUE,IN4: IU4,0))

etc etc (This works OK)

So, essentially I need something that looks at your forlmula (in cells IM)
and changes the colours in column "U" based on the return from your formula.

I hope this helps.

Thanks




"Max" wrote:

With cell A2 housing the earlier formula returning the numbers 1 - 8, think
you could try tinkering with the sheet sub below to fill-color A2, with the
font color for A2 set to match the fill color so as to mask the underlying
number returned by the formula

To install:
Copy the sub
Right-click on the sheet tab View Code
Paste the sub into the code window on the right
Press Alt + Q to get back to Excel

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer, fcolor As Integer
Set Target = [A2]
Select Case Target
Case 1
icolor = 6
fcolor = 6
Case 2
icolor = 12
fcolor = 12
Case 3
icolor = 7
fcolor = 7
Case 4
icolor = 53
fcolor = 53
Case 5
icolor = 15
fcolor = 15
Case 6
icolor = 42
fcolor = 42
Case 7
icolor = 50
fcolor = 50
Case 8
icolor = 13
fcolor = 13
Case Else
End Select

With Target
.Interior.ColorIndex = icolor
.Font.ColorIndex = fcolor
End With

End Sub

--
Max
Singapore
---
"John Calder" wrote:
Max

The person I am doing this for is really keen to have colour as the
identifier but if all else fails I will definately look at your option

  #6  
Old May 21st, 2010, 07:34 AM posted to microsoft.public.excel.newusers
Max
external usenet poster
 
Posts: 8,574
Default Conditional Formatting

John, adapt the sub below (courtesy of a reply by MVP Jeff in another forum)
to suit the range to be colored, depending on the numbers returned by the
formulas in that range. As-is, it presumes that the range you want colored is
IM2:IM100, which contains formulas returning the numbers 1-8. I've tested it
and it works fine

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor, fcolor As Integer
Dim rng As Range
For Each rng In Range("IM2:IM100")
Select Case rng.Value
Case 1
icolor = 6
fcolor = 6
Case 2
icolor = 12
fcolor = 12
Case 3
icolor = 7
fcolor = 7
Case 4
icolor = 53
fcolor = 53
Case 5
icolor = 15
fcolor = 15
Case 6
icolor = 42
fcolor = 42
Case 7
icolor = 50
fcolor = 50
Case 8
icolor = 13
fcolor = 13
Case Else
End Select
With rng
.Interior.ColorIndex = icolor
.Font.ColorIndex = fcolor
End With
Next
End Sub

--
Max
Singapore
---
  #7  
Old May 24th, 2010, 03:42 AM posted to microsoft.public.excel.newusers
John Calder
external usenet poster
 
Posts: 192
Default Conditional Formatting

Max

Once again thanks for your help. Your code does indeed work but not how I
intended it. The problem is that the cells I need coloured (U2:U51) also have
a manual input in each one. You solution as I understand it requires a
formula to be placed in each cell. Therfore I would not be able to place the
manual input that is required in each cell. The colour of each cell is not
dependant on the input of each cell it is dependant on the number that is
returned from your intial formula.

So, I have 8 tick boxes in cells C:J
Depending on which tick box I choose it returns a "TRUE" somewhere in the
range IN:IU
Your formula (in cell IM) returns the number in which the "TRUE" is returned.
What I need is that the corresponding cell in column U returns a colour
which represents the number returned by your formula. I can then place an
input (ie $300) into this cell.

eg:

1 = Yellow
2 = Red
3 = Pink
4 = Light Green
5 = Light Orange:
6 = Grey
7 = Light Blue
8 = Green

I hope I have managed to explain my situation a little better for you.

Thanks




"Max" wrote:

John, adapt the sub below (courtesy of a reply by MVP Jeff in another forum)
to suit the range to be colored, depending on the numbers returned by the
formulas in that range. As-is, it presumes that the range you want colored is
IM2:IM100, which contains formulas returning the numbers 1-8. I've tested it
and it works fine

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor, fcolor As Integer
Dim rng As Range
For Each rng In Range("IM2:IM100")
Select Case rng.Value
Case 1
icolor = 6
fcolor = 6
Case 2
icolor = 12
fcolor = 12
Case 3
icolor = 7
fcolor = 7
Case 4
icolor = 53
fcolor = 53
Case 5
icolor = 15
fcolor = 15
Case 6
icolor = 42
fcolor = 42
Case 7
icolor = 50
fcolor = 50
Case 8
icolor = 13
fcolor = 13
Case Else
End Select
With rng
.Interior.ColorIndex = icolor
.Font.ColorIndex = fcolor
End With
Next
End Sub

--
Max
Singapore
---

  #8  
Old May 24th, 2010, 07:07 AM posted to microsoft.public.excel.newusers
Max
external usenet poster
 
Posts: 8,574
Default Conditional Formatting

John,

Think its best that you start a new thread in this MS vba forum:
http://social.msdn.microsoft.com/For...isvvba/threads

In your new post there, do mention your Excel version and ensure that
complete info is provided

MS has posted a msg that all xl newsgroups (like this one) will be closed in
end May 2010. As your query requires a vba solution, the forum above is
appropriate and you should be able to gather the expertise of vba responders
to get you going. The above forum is amongst those directed by MS for ng
posters, and is MS' way forward for queries wef Jun 2010. All the best to you
and farewell ..
---
Max
Singapore
-----
  #9  
Old May 25th, 2010, 02:07 AM posted to microsoft.public.excel.newusers
John Calder
external usenet poster
 
Posts: 192
Default Conditional Formatting

Max

You have been a huge help. Thanks a lot. I will repost as you suggested.

Thank You


"Max" wrote:

John,

Think its best that you start a new thread in this MS vba forum:
http://social.msdn.microsoft.com/For...isvvba/threads

In your new post there, do mention your Excel version and ensure that
complete info is provided

MS has posted a msg that all xl newsgroups (like this one) will be closed in
end May 2010. As your query requires a vba solution, the forum above is
appropriate and you should be able to gather the expertise of vba responders
to get you going. The above forum is amongst those directed by MS for ng
posters, and is MS' way forward for queries wef Jun 2010. All the best to you
and farewell ..
---
Max
Singapore
-----

 




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


All times are GMT +1. The time now is 05:27 PM.


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