Macro to format cells from one worksheet to another

[Rick93636]

I have written a VBA that will format a cell in one worksheet based on the value of a cell in another worksheet. It works fine. My question is this: I have 312 cells to format. Each cell on the main worksheet looks to a different cell on the other worksheet. Is there an easier way than to write the multiple lines for each cell?

Here's the VBA:

Sub CopyTransposeAS()
'
' CopyTransposeAS Macro
' Copy and Transpose Anscillary Service
'
' Keyboard Shortcut: Ctrl+Shift+A
'
ActiveSheet.Name = "myDataTable"
Sheets("myDataTable").Select
Sheets("myDataTable").Copy After:=Workbooks("FOC daily schedule.xlsm").Sheets _
(2)
ActiveWorkbook.Save
Sheets("myDataTable").Select
Range("H7").Select
Select Case Range("h7")
Case 1 To 100
Sheets("Schedule").Select
Range("H12").Select

With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Select

End Sub

In this one the H12 cell of the Schedule worksheet looks to the H7 cell of the myDataTable worksheet. If the H7 cell has a value of 1-100 then the H12 cell will have yellow fill.

So...do I have to copy this and change the cell reference 311 more times?

Thanks

 

[Rick93636]

This was solved with ElseIf command.