briannnn
03-20-2020, 10:58 AM
Hello everyone, the VBA task I am currently working on requires me to make codes that allow users to enter the font color of two categories (Fruits & Veg) and the inferior color of the herbs, and the excel will follow his/her inputs
....I have tried for around four hours and is getting to the edge of collapsing....Please help and many thanks.
Sub Task2()
Dim i As Long
Dim RowCount As Long
Dim FruitColor As Variant
Dim VegColor As Variant
Dim HerbColor As Variant
Dim category As Range
Dim product As Range
Dim quantity As Range
With Sheets("Sheet1")
RowCount = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To RowCount
Set category = .Cells(i, 1)
Set product = .Cells(i, 2)
Set quantity = .Cells(i, 3)
' create 3 input box functions here to obtain user's preference:
' 2 input box functions for Fruits (Blue or Magenta), and
' Vegetables (Red or Cyan); 1 input box function for interior
' color of Herbs (Yellow or Green)
FruitColor = InputBox("Please enter the font color of Fruits:" & vbCrLf & "Blue or Magenta", "Fruits Color")
If FruitColor = "Blue" Then
If category = "Fruits" Then
category.Font.Color = vbBlue
product.Font.Color = vbBlue
quantity.Font.Color = vbBlue
End If
Else
If category = "Fruits" Then
category.Font.Color = vbMagenta
product.Font.Color = vbMagenta
quantity.Font.Color = vbMagenta
End If
End If
VegColor = InputBox("Please enter the font color of Vegetables:" & vbCrLf & "Red or Cyan", "Vegetables Color")
If VegColor = "Red" Then
If category = "Vegetables" Then
category.Font.Color = vbRed
product.Font.Color = vbRed
quantity.Font.Color = vbRed
End If
Else
If category = "Vegetables" Then
category.Font.Color = vbCyan
product.Font.Color = vbCyan
quantity.Font.Color = vbCyan
End If
End If
HerbColor = InputBox("Please enter the interior color of Herbs:" & vbCrLf & "Yellow or Green", "HerbColor")
If HerbColor = "Yellow" Then
If category = "Herbs" Then
category.Font.Color = vbYellow
product.Font.Color = vbYellow
quantity.Font.Color = vbYellow
End If
Else
If category = "herbs" Then
category.Interior.Color = vbGreen
product.Interior.Color = vbGreen
quantity.Interior.Color = vbGreen
End If
End If
Next i
End With
End Sub
....I have tried for around four hours and is getting to the edge of collapsing....Please help and many thanks.
Sub Task2()
Dim i As Long
Dim RowCount As Long
Dim FruitColor As Variant
Dim VegColor As Variant
Dim HerbColor As Variant
Dim category As Range
Dim product As Range
Dim quantity As Range
With Sheets("Sheet1")
RowCount = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To RowCount
Set category = .Cells(i, 1)
Set product = .Cells(i, 2)
Set quantity = .Cells(i, 3)
' create 3 input box functions here to obtain user's preference:
' 2 input box functions for Fruits (Blue or Magenta), and
' Vegetables (Red or Cyan); 1 input box function for interior
' color of Herbs (Yellow or Green)
FruitColor = InputBox("Please enter the font color of Fruits:" & vbCrLf & "Blue or Magenta", "Fruits Color")
If FruitColor = "Blue" Then
If category = "Fruits" Then
category.Font.Color = vbBlue
product.Font.Color = vbBlue
quantity.Font.Color = vbBlue
End If
Else
If category = "Fruits" Then
category.Font.Color = vbMagenta
product.Font.Color = vbMagenta
quantity.Font.Color = vbMagenta
End If
End If
VegColor = InputBox("Please enter the font color of Vegetables:" & vbCrLf & "Red or Cyan", "Vegetables Color")
If VegColor = "Red" Then
If category = "Vegetables" Then
category.Font.Color = vbRed
product.Font.Color = vbRed
quantity.Font.Color = vbRed
End If
Else
If category = "Vegetables" Then
category.Font.Color = vbCyan
product.Font.Color = vbCyan
quantity.Font.Color = vbCyan
End If
End If
HerbColor = InputBox("Please enter the interior color of Herbs:" & vbCrLf & "Yellow or Green", "HerbColor")
If HerbColor = "Yellow" Then
If category = "Herbs" Then
category.Font.Color = vbYellow
product.Font.Color = vbYellow
quantity.Font.Color = vbYellow
End If
Else
If category = "herbs" Then
category.Interior.Color = vbGreen
product.Interior.Color = vbGreen
quantity.Interior.Color = vbGreen
End If
End If
Next i
End With
End Sub