Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do i find my old questions and answers | Excel Discussion (Misc queries) | |||
Past Questions and Answers | Excel Discussion (Misc queries) | |||
Answers to questions posing more questions in a workbook | Excel Worksheet Functions | |||
Links to Answers to questions | Excel Discussion (Misc queries) | |||
Customizing help questions/answers | Excel Discussion (Misc queries) |