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

[Guest]

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.

 

[Bob Phillips]

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

 

[Tom Ogilvy]

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

 

[Tim M.]

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.

 

[Dave Peterson]

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

 

[Guest]

THANKS EVERYONE FOR YOUR HELP!!!!!!!!!!!!!!!!!!
THIS IS WHAT I CAME UP WITH
=SUMIF(B:B,102,C:C)+SUMIF(B:B,102,D)+SUMIF(B:B,102,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