VBA – Cells / Range formatting and apply formulas

Spread the love

 

Work with range object helps to power full control desiging and manipulation and plotting the data. In this topic we are trying to cover maximum uses of the range through the VBA script.

Range objects help to data formatting, manipulation / analysis and plotting.

Range().value = “ “ Pass Excel Function within “” , like “=vlookup()” , it automatically changes the references
Range (). Font. Color = 65535 ‘cell’s color
Range().Font.Color = -16776961 ‘cells text color
Range().Font.Size = 12 cells size
Range().Style = “Currency” currency type selection
Range().Style = “Percent” percentage
Range().NumberFormat = “0.0” decimal formatting with
Range().Number Format = “0” decimal formatting with
Range().Style = “Comma” assign comma in values

Copy below code and apply

Sub Range_uses()

‘==========use with for formatting on fixed range ================================
With Range(“b2:c25”)
.Value = “=rand()”
.Interior.Color = 65535
.Font.Color = -16776961
.Font.Size = 12
.Style = “Currency”
.NumberFormat = “0.0%”
.Style = “Percent”
.Style = “Comma”

                       End With

End Sub

VBA – Apply  formula on Range from worksheets

Copy below code

Sub appy_formula()

      range(“e2:e20″).formula = ” “

end sub

 

Copy a formula

 

  1. Now try to put copied formula in VBA
  2. Now edit and paste formulas between double quotes  –   range(“e2:e20”).formula = ” “
  3. Now it will look like this –   range(“e2:e20”).formula = “=round(b2*5,2) & ”      VBA” “
  4. Compile error will appear
  5. Change single quotes to double quotes which is mentioned in the image as corrected line, the string should be with “”      “” when formula copied directly from the cell
  6. So the correct line as – range(“e2:e20”).formula = “=round(b2*5,2) & “”   VBA””  “

Copy a formula 3