Solved: VBA Newbie

[Denny White]

I would like to know if someone can help me with a very simple question.

I have an extensive VBA program written by someone who has moved up to greener pastures and is no longer available to help me.

I have a spread sheet (form) that has data imported into it. One cell in the sheet has one of four possible data entries.

HD1, HD1, QM46, or SM74. These represent four different printing presses.

Toward the bottom of the spreadsheet there are three sets of boxes drawn to represent the printed sheet. I would like a code that can either be in a macro that is already set for printing out the sheets or as a formula that will read what is in the cell and select the set of cells and color them in so that when looking at the form the press sheet is highlighted to very quickly show what press it will print on.

I am far from a genius at this.

Any help will be very greatly appreciated.

[georgiboy]

Are you able to post an example spreadsheet using the aditional options?

[Denny White]

I attempted to make a bmp, jpeg etc. All of them exceeded allowable size.

Simplest description would be the press type is cell A1.

Then the cells I would like to color are as follows.

HD1 & 2 would be cells A5 through C10.
SM74 would be cells E5 through G10.
QM46 would be cells H5 through I10.

So whatever is in A1 will tell the spreadsheet to print in the necessary cells.

If HD1 or HD2 then shade in A5 to C10.
If SM74 then shade in E5 to G10.
If QM46 then shade in H5 to I10.

I know what I need to have happen. I just don't have the proper training yet to get the correct syntax.

[georgiboy]

If you wanted to just colour the cells then you could put this in the worksheet module

[VBA]Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1")) Is Nothing Then

If Left(Range("A1").Value, 2) = "HD" Then
Range("A5:C10").Interior.ColorIndex = 3
Else
Range("A5:C10").Interior.ColorIndex = 0
End If

If Range("A1").Value = "SM74" Then
Range("E5:G10").Interior.ColorIndex = 3
Else
Range("E5:G10").Interior.ColorIndex = 0
End If

If Range("A1").Value = "QM46" Then
Range("H5:I10").Interior.ColorIndex = 3
Else
Range("H5:I10").Interior.ColorIndex = 0
End If

End If
End Sub[/VBA]

Hope this helps

[JeffT]

Denny

If you just want to colour the cels then you could just use conditional formatting (under Format / Conditional formatting and change condition 1 from "Cell Value Is" to "Formula Is" then put in the formula below) no VBA necessary.

Use

=OR($A$1="HD1",$A$1="HD2")

as the condition for A5:C10 and set the colour (Format / Patterns)

For the other ranges use:-

=$A$1="QM46"

or

=$A$1="SM74"

and set the colours

[Denny White]

Thank You both for your very able assistance. That will do exactly what I want it to do ! ! ! ! ! ! !

[Denny White]

Very excellent help here ! ! !