Use WITH statement as a condition of IF in VBA

[CooperJExcel]

I am teaching a basic Excel course and I want to automate the grading process. I have an exercise that the students will complete but I want to use VBA to validate the values as well as the formatting. To start, I recorded the VBA macro to do the exercise and then cleaned it up a little bit.

Sub Lesson2Copy()


' Lesson2Copy Macro


Range("H1:K1").Select
With Selection
.Interior.Pattern = xlSolid
.Interior.PatternColorIndex = xlAutomatic
.Interior.ThemeColor = xlThemeColorAccent1
.Interior.TintAndShade = 0.599993896298105
.Interior.PatternTintAndShade = 0
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 45
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
.Borders.LineStyle = xlContinuous
.Borders.ColorIndex = xlAutomatic
.Borders.TintAndShade = 0
.Borders.Weight = xlMedium
.Font.Name = "Agency FB"
.Font.Size = 11
End With

Range("H1").Value = "Item"
Range("I1").Value = "LBs"
Range("J1").Value = "Cost/LB"
Range("K1").Value = "Total Cost"

Range("H2").Value = "Potatoes"
Range("H3").Value = "Carrots"
Range("H4").Value = "Apples"
Range("H5").Value = "Onions"
Range("H6").Value = "Strawberries"
Range("H7").Value = "Spinach"
Range("H8").Value = "Watermelon"
Range("H9").Value = "*Anything highlighted in red is over $10.00"
Range("I2").Value = "50"
Range("I3").Value = "25"
Range("I4").Value = "5"
Range("I5").Value = "10"
Range("I6").Value = "2"
Range("I7").Value = "1.5"
Range("I8").Value = "6"
Range("J2").Value = "0.31"
Range("J3").Value = "0.54"
Range("J4").Value = "0.83"
Range("J5").Value = "0.55"
Range("J6").Value = "1.47"
Range("J7").Value = "1.35"
Range("J8").Value = "0.32"
Range("K2").Value = "=RC[-2]*RC[-1]"
Range("K2").AutoFill Destination:=Range("K2:K8"), Type:=xlFillDefault
Range("G2").Value = "Produce List"

Range("G2:G8").Select
With Selection
.Interior.Pattern = xlSolid
.Interior.PatternColorIndex = xlAutomatic
.Interior.ThemeColor = xlThemeColorAccent1
.Interior.TintAndShade = -0.499984740745262
.Interior.PatternTintAndShade = 0
.Font.ThemeColor = xlThemeColorDark1
.Font.TintAndShade = 0
.Font.Name = "Batang"
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.MergeCells = True
.Orientation = 90
.Font.Bold = True
End With

Columns("G:G").EntireColumn.AutoFit

Range("H2:K8").Select
With Selection
.Borders.LineStyle = xlContinuous
.Borders.ColorIndex = 0
.Borders.TintAndShade = 0
.Borders.Weight = xlThin
.Font.Name = "Agency FB"
End With

Range("H9").Font.Name = "Agency FB"

Range("K2:K3").Interior.Color = 255

Range("K2:K8").Select
With Selection
.Font.Bold = True
.NumberFormat = "$#,##0.00"
End With

End Sub

I was thinking I would use variables to calculate the score. For example

Sub Example ()

Dim FormatScore as Integer
Dim FormatTot as Integer

FormatScore = 0
FormatTot = 0

'Testing to see if A1 has the correct font
If Range("A1").font.name = "Arial" Then
FormatScore = FormatScore + 1
FormatTot = FormatTot + 1
'Add 1 point to the total but not to the score if A1 does not have the correct font
Else
FormatTot = FormatTot + 1

End Sub

Then at the end I would calculate the score with the variables. However, in my original code I have a few instances when I select cells and then use a WITH Selection statement. I would like to be able to somehow house the commands within my WITH statement all into one IF statement. I know I could do this with a dozen IF statements but that sounds like a headache to me if I can avoid it. Is there an easier way to do this?

Let me know if it doesn't make sense.

 

[thes4s67]

I think I get what you mean. Have you thought of using arrays? You can create 3 arrays: the first array will be the student's work of the selected range, the second is the answer key, and the third is to store wrong answers. You can compare if the first and second array are similar, if not add an element in the wrong answer array. This might be where you will have the most if statements depending on how many points you plan on giving per answer.

 

[CooperJExcel]

I haven't used arrays a lot and am not very familiar. If I used arrays, would formatting be captured as well or would it only compare values?