How do I change a workbook based on answers to questions?
 

I would like to have my wookbook change based on the input into a cell. Under
conditional formatting I cant do it. I want to be able to change more the the
"color" in a cell. THANK YOU FOR ANY ADVICE.

How do I change a workbook based on answers to questions?
 

CF allows you to change the font colour, cell colour, font style and border.
What else do you need?

--

HTH

RP

"Markitos" wrote in message
...
I would like to have my wookbook change based on the input into a cell.
Under
conditional formatting I cant do it. I want to be able to change more the
the
"color" in a cell. THANK YOU FOR ANY ADVICE.

How do I change a workbook based on answers to questions?
 

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
if target.count 1 then exit sub
if isempty(target) then exit sub
If target.Address = "$B$9" Then
Select Case Target.Value
Case 1
target.Interior.colorIndex = 4
Target.Font.bold = True
Target.Font.Italic = True
Case 2
target.Interior.colorIndex = 5
Target.Font.bold = True
Target.Font.Italic = True
End Select
End if
End Sub

This is starter code since you didn't give any details on what, when, why,
where or how.

This fires when B9 is edited (code placed in the sheet module of the sheet
where you want this behavior).

there is also a calculate event

See Chip Pearson's page on events
http://www.cpearson.com/excel/events.htm

--
Regards,
Tom Ogilvy


"Markitos" wrote in message
...
I would like to have my wookbook change based on the input into a cell.
Under
conditional formatting I cant do it. I want to be able to change more the
the
"color" in a cell. THANK YOU FOR ANY ADVICE.

How do I change a workbook based on answers to questions?
 

Tom,

-- Just trying to learn how this works. Can you explain
what the "target.count 1" does?

Then, how does the Case statement work? What causes the
macro to look at Case 1 vs. Case 2 to color the cell.

Thank you.

-----Original Message-----
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
if target.count 1 then exit sub
if isempty(target) then exit sub
If target.Address = "$B$9" Then
Select Case Target.Value
Case 1
target.Interior.colorIndex = 4
Target.Font.bold = True
Target.Font.Italic = True
Case 2
target.Interior.colorIndex = 5
Target.Font.bold = True
Target.Font.Italic = True
End Select
End if
End Sub

This is starter code since you didn't give any details on
what, when, why,
where or how.

This fires when B9 is edited (code placed in the sheet
module of the sheet
where you want this behavior).

there is also a calculate event

See Chip Pearson's page on events
http://www.cpearson.com/excel/events.htm

--
Regards,
Tom Ogilvy


"Markitos" wrote in
message
...
I would like to have my wookbook change based on the
input into a cell.
Under
conditional formatting I cant do it. I want to be able
to change more the
the
"color" in a cell. THANK YOU FOR ANY ADVICE.


.

How do I change a workbook based on answers to questions?
 

Target.count returns the number of cells in the Target range.

If you selected A1:A10 and hit the delete key, you'd be changing 10 cells. (If
you typed "asdf" and hit ctrl-enter with a1:a10 still selected, you'd be
changing 10 cells.)

Tom's code will check the number of cells you're changing. If it's more than
one, it simply exits the procedure.

Take a look at "select Case" in VBA's help.

You'll find that it's a neat way to write code that's equivalent to:

if target.value = 1 then
'do something
elseif target.value = 2 then
'do something else
elseif target.value = 3 then
'do something completely different.
end if


"Tim M." wrote:

Tom,

-- Just trying to learn how this works. Can you explain
what the "target.count 1" does?

Then, how does the Case statement work? What causes the
macro to look at Case 1 vs. Case 2 to color the cell.

Thank you.

-----Original Message-----
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
if target.count 1 then exit sub
if isempty(target) then exit sub
If target.Address = "$B$9" Then
Select Case Target.Value
Case 1
target.Interior.colorIndex = 4
Target.Font.bold = True
Target.Font.Italic = True
Case 2
target.Interior.colorIndex = 5
Target.Font.bold = True
Target.Font.Italic = True
End Select
End if
End Sub

This is starter code since you didn't give any details on
what, when, why,
where or how.

This fires when B9 is edited (code placed in the sheet
module of the sheet
where you want this behavior).

there is also a calculate event

See Chip Pearson's page on events
http://www.cpearson.com/excel/events.htm

--
Regards,
Tom Ogilvy


"Markitos" wrote in
message
...
I would like to have my wookbook change based on the
input into a cell.
Under
conditional formatting I cant do it. I want to be able
to change more the
the
"color" in a cell. THANK YOU FOR ANY ADVICE.


.


--

Dave Peterson

How do I change a workbook based on answers to questions?
 

THANKS EVERYONE FOR YOUR HELP!!!!!!!!!!!!!!!!!!
THIS IS WHAT I CAME UP WITH
=SUMIF(B:B,102,C:C)+SUMIF(B:B,102,D:D)+SUMIF(B:B,1 02,E:E)
JUST CHANGE TO MATCH COLUMNS AND THE NUMBER IS WHAT THE ACCT. IS
EXAMPLE
Asset/ Payable acc. 101
Opening B. 0
New B. 30
Accounts 523 10
101 10
524
101
203
523
523
102 -1000
102 -2500
Month Rec.
Acc. Amount
101 30
102 -3500
103 0
104 0
105 0
203 0
523 10
524 0

THEN THE REPORTS SHEET would look like this.
Year Rec.
Acc. Amount
101 30
102 -3500
103 0
104 0
105 0
203 0
523 10
524 0

"Markitos" wrote:

I would like to have my wookbook change based on the input into a cell. Under
conditional formatting I cant do it. I want to be able to change more the the
"color" in a cell. THANK YOU FOR ANY ADVICE.