Font and Background Color in Excel VBA :-

Font and Background Color in Excel VBA:-

The format to set the font color is

cells(i,j).Font.Color=RGB(x,y,x), where x ,y , z can be any number between 1 and 255

For example

cells(1,1).Font.Color=RGB(255,255,0) will change the font color to yellow

The format to set the cell's background color is

cells(i,j).Interior.Color=RGB(x,y,x), where x ,y , z can be any number between 1 and 255

Here is the VBA code:-

Private Sub CommandButton1_Click()
Randomize Timer
Dim i, j, k As Integer
i = Int(255 * Rnd) + 1
j = Int(255 * Rnd) + 1
k = Int(255 * Rnd) + 1
Cells(1, 1).Font.Color = RGB(i, j, k)
Cells(2, 1).Interior.Color = RGB(j, k, i)
End Sub

Calculating BMI :-

The formula for calculating BMI is

BMI=wieght(/(height2)

Here is the VBA code:-

Private Sub Calc_BMI()
Dim weight, height, bmi, x As Single

weight = Cells(2, 2)
height = Cells(3, 2)
bmi = (weight) / height ^ 2
Cells(4, 2) = Round(bmi, 1)
If bmi <= 15 Then Cells(5, 2) = "Under weight" ElseIf bmi > 15 And bmi <= 25 Then
Cells(5, 2) = "Optimum weight"
Else
Cells(5, 2) = "Over weight"
End If
End Sub

Calculate the interest rate :-

To solve this puzzle, we need to calculate the initial investment based on the interest rate and the length of a period, usually in years. The formula is

WorksheetFunction.PV(rate, N, periodic payment, amount, due)

where rate is the interest rate, N is the length of the period and amount is the amount borrowed.

Here is the VBA code:-

Private Sub Calc_InterestRate()
Dim F_Money, Int_Rate, Investment As Double
Dim numYear As Single
F_Money = Cells(2, 2)
Int_Rate = (Cells(3, 2) / 100)
numYear = Cells(4, 2)
Investment = PV(Int_Rate, numYear, 0, F_Money, 1)
Cells(5, 2) = Format(-Investment, "$##,###,##0.00")
End Sub

Prime Number or Not :-

Private Sub Check_PrimeorNot()

Dim N, D As Single
Dim tag As String
N = Cells(2, 2)

Select Case N
Case Is < 2 MsgBox "It is not a prime number" Case Is = 2 MsgBox "It is a prime number" Case Is > 2

D = 2
Do
If N / D = Int(N / D) Then
MsgBox "It is not a prime number"
tag = "Not Prime"
Exit Do
End If
D = D + 1
Loop While D <= N - 1 If tag <> "Not Prime" Then
MsgBox "It is a prime number"
End If
End Select
End Sub

Calculated the Sum Passed & Failed :-

This program is to calculated the Sum Passed & Failed:-

Private Sub Calc_Sumpassed_SumFailed()
Dim rng As Range, i As Integer
Dim mark, sumFail, sumPass As Single
sumFail = 0
sumPass = 0
Set rng = Range("A1:A10")
For i = 1 To 10
mark = rng.Cells(i).Value
Select Case mark
Case Is < 50 sumFail = sumFail + mark Case Is >= 50
sumPass = sumPass + mark
End Select
Next i
MsgBox "The sum of Failed marks is" & Str(sumFail) & vbCrLf & "The sum of Passed marks is" & Str(sumPass)
End Sub