Basic Macro help needed!

  • Thread starter Thread starter Matt B
  • Start date Start date
M

Matt B

Thanks, its good to know that it is possible!

Unforntunately your code didnt seem to work...maybe due to the cell
references, but im unsure:

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
 
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
 
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
 
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
 
You may? not want this to run on all worksheets and in all cells. So, you
would need to restrict based on your requirements.
 
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
 
Back
Top