Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Basic Macro help needed!
Thanks, its good to know that it is possible!
Unforntunately your code didnt seem to work...maybe due to the cell references, but im unsu Im going to be entering data (Mr, Mrs etc) in c11 and would want cells d11, e11 and f11 to fill in a colour dependant upon what value was entered. Dont know if that would make any difference. Any help would be much appreciated! If "Mrs Trevor Shuttleworth wrote in message ... Matt your second question is a specific case of the first. Try this: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Rows("1:1")) Is Nothing Then Exit Sub If UCase(Target.Value) = "MR" Then _ Range(Cells(3, Target.Column), _ Cells(5, Target.Column)).Interior.ColorIndex = 45 If UCase(Target.Value) = "MRS" Then _ Range(Cells(3, Target.Column), _ Cells(5, Target.Column)).Interior.ColorIndex = 3 End Sub Regards Trevor "Matt B" wrote in message om... Hi, Ive just started to use macros and I would appreciate any help on the following ideas! 1) Is it possible to have a macro "always" running...so for example if i entered data in one cell it would automatically format/copy/etc into another cell. 2)And secondly, is it possible to say, fill cells with a colour dependant upon the data entered in anothercell . eg: if a1="Mr"(or active cell=1) then cells a3 a4 and a5 would fill in orange. if a1="Mrs" then cells a3 a4 and a5 would fill red. Thanks in advance for the help. Matt |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Basic Macro help needed!
Hi,
Ive just started to use macros and I would appreciate any help on the following ideas! 1) Is it possible to have a macro "always" running...so for example if i entered data in one cell it would automatically format/copy/etc into another cell. 2)And secondly, is it possible to say, fill cells with a colour dependant upon the data entered in anothercell . eg: if a1="Mr"(or active cell=1) then cells a3 a4 and a5 would fill in orange. if a1="Mrs" then cells a3 a4 and a5 would fill red. Thanks in advance for the help. Matt |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Basic Macro help needed!
Put this with your code into the code for "ThisWorkbook" and it will run
every time there is a change to the workbook. Careful though because if the code itself changes the sheet, that change will trigger the same process Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) End Sub As for Cell background color: Cells(1, 3).Interior.ColorIndex = 6 6 = Yelow Other numbers represent other colors. - Pikus --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Basic Macro help needed!
Matt
your second question is a specific case of the first. Try this: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Rows("1:1")) Is Nothing Then Exit Sub If UCase(Target.Value) = "MR" Then _ Range(Cells(3, Target.Column), _ Cells(5, Target.Column)).Interior.ColorIndex = 45 If UCase(Target.Value) = "MRS" Then _ Range(Cells(3, Target.Column), _ Cells(5, Target.Column)).Interior.ColorIndex = 3 End Sub Regards Trevor "Matt B" wrote in message om... Hi, Ive just started to use macros and I would appreciate any help on the following ideas! 1) Is it possible to have a macro "always" running...so for example if i entered data in one cell it would automatically format/copy/etc into another cell. 2)And secondly, is it possible to say, fill cells with a colour dependant upon the data entered in anothercell . eg: if a1="Mr"(or active cell=1) then cells a3 a4 and a5 would fill in orange. if a1="Mrs" then cells a3 a4 and a5 would fill red. Thanks in advance for the help. Matt |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Basic Macro help needed!
You may? not want this to run on all worksheets and in all cells. So, you
would need to restrict based on your requirements. -- Don Guillett SalesAid Software "pikus " wrote in message ... Put this with your code into the code for "ThisWorkbook" and it will run every time there is a change to the workbook. Careful though because if the code itself changes the sheet, that change will trigger the same process Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) End Sub As for Cell background color: Cells(1, 3).Interior.ColorIndex = 6 6 = Yelow Other numbers represent other colors. - Pikus --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Basic Macro help needed!
Matt
well,first the code needs to be in the Worksheet Class module for the sheet where you want this to work. Second, it's coded to answer the example you quoted in your OP. Try this: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("C11")) Is Nothing Then Exit Sub If UCase(Target.Value) = "MR" Then _ Range("D11:F11").Interior.ColorIndex = 45 If UCase(Target.Value) = "MRS" Then _ Range("D11:F11").Interior.ColorIndex = 3 End Sub Regards Trevor "Matt B" wrote in message ... Thanks, its good to know that it is possible! Unforntunately your code didnt seem to work...maybe due to the cell references, but im unsu Im going to be entering data (Mr, Mrs etc) in c11 and would want cells d11, e11 and f11 to fill in a colour dependant upon what value was entered. Dont know if that would make any difference. Any help would be much appreciated! If "Mrs Trevor Shuttleworth wrote in message ... Matt your second question is a specific case of the first. Try this: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Rows("1:1")) Is Nothing Then Exit Sub If UCase(Target.Value) = "MR" Then _ Range(Cells(3, Target.Column), _ Cells(5, Target.Column)).Interior.ColorIndex = 45 If UCase(Target.Value) = "MRS" Then _ Range(Cells(3, Target.Column), _ Cells(5, Target.Column)).Interior.ColorIndex = 3 End Sub Regards Trevor "Matt B" wrote in message om... Hi, Ive just started to use macros and I would appreciate any help on the following ideas! 1) Is it possible to have a macro "always" running...so for example if i entered data in one cell it would automatically format/copy/etc into another cell. 2)And secondly, is it possible to say, fill cells with a colour dependant upon the data entered in anothercell . eg: if a1="Mr"(or active cell=1) then cells a3 a4 and a5 would fill in orange. if a1="Mrs" then cells a3 a4 and a5 would fill red. Thanks in advance for the help. Matt |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Basic help needed using sumproduct to do a league table | Excel Worksheet Functions | |||
Basic example formula needed to link between sheets add/subtract | New Users to Excel | |||
Basic Macro Help | Excel Discussion (Misc queries) | |||
Basic IF function help needed | New Users to Excel | |||
Multiply two colums; BASIC help needed | New Users to Excel |