In this example you will learn about the cell alignment in Excel VBA.
The Problem:
Assume that, We have a table that shows, Mobile Brand Name, and selling quantity as below:
What you need to do is, create a Grade column and show the grade using IF statement and put a VBA Button. Also add the cell alignment and full row background color based on grade.
The Grade scale is: If above 90000, A+, 70000, A, 50000, B+, 30000, B, 10000, C+, F.
The Solution:
Design the table like below:
Now in Module write the below codes:
The Result:
After entering the above final code into the Module, Press F5 or Click on Play to run it. The result will display like below:
The Problem:
Assume that, We have a table that shows, Mobile Brand Name, and selling quantity as below:
Image 1: Sample data
What you need to do is, create a Grade column and show the grade using IF statement and put a VBA Button. Also add the cell alignment and full row background color based on grade.
The Grade scale is: If above 90000, A+, 70000, A, 50000, B+, 30000, B, 10000, C+, F.
The Solution:
Design the table like below:
Image 2: Data Table
Now in Module write the below codes:
Option Explicit Sub grade_analysis() Dim x As Integer Dim y As Long Dim z As String For x = 2 To 9 y = Cells(x, 2).Value If y >= 90000 Then Cells(x, 3).Value = "A+" Cells(x, 3).HorizontalAlignment = xlCenter Cells(x, 3).Interior.Color = RGB(255, 155, 250) ElseIf y >= 70000 Then Cells(x, 3).Value = "A" Cells(x, 3).HorizontalAlignment = xlCenter Cells(x, 3).Interior.Color = RGB(255, 255, 200) ElseIf y >= 50000 Then Cells(x, 3).Value = "B+" Cells(x, 3).HorizontalAlignment = xlCenter Cells(x, 3).Interior.Color = RGB(255, 255, 150) ElseIf y >= 30000 Then Cells(x, 3).Value = "B" Cells(x, 3).HorizontalAlignment = xlCenter Cells(x, 3).Interior.Color = RGB(255, 205, 100) ElseIf y >= 10000 Then Cells(x, 3).Value = "C+" Cells(x, 3).HorizontalAlignment = xlCenter Cells(x, 3).Interior.Color = RGB(255, 155, 50) Else Cells(x, 3).Value = "F" Cells(x, 3).HorizontalAlignment = xlCenter Cells(x, 3).Interior.Color = RGB(155, 255, 105) End If Next x End Sub
The Result:
After entering the above final code into the Module, Press F5 or Click on Play to run it. The result will display like below:
Image 3: Final Result
|||| Please SUBSCRIBE our YouTube Channel ||||
https://www.youtube.com/channel/UCIWaA5KCwZzBGwtmGIOFjQw
|||| Please SUBSCRIBE our YouTube Channel ||||
https://www.youtube.com/channel/UCIWaA5KCwZzBGwtmGIOFjQw