asked on

how to format with VBA?

How can i automatically get the result needed shown in the attached workbook with VBA.

i have attached two sheets, the first one is the currently what i have and the next one is the desired result that i am looking for.

i need to get this done with VBA. including the coloring where the condition is if those cells starting from C4 with percentage % if itis i above 0.7 then green and if lower then 0.1 then orange and the rest no color.

the first row is formatted with superscript and second row is changed to bold and italic is removed.

any help is appreciated.
EE.xlsx

Norie

Skylar

Is the sheet 'CURRENT' what you end up with after the last piece of code I posted?

Do you want to add the formatting into that code?

Fabrice Lambert

Hi,

Formatting is done with the range object:

Dim wb As Excel.Workbook
Set wb = ThisWorkbook

Dim ws As Excel.Worksheet
Set ws = wb.Worksheets("CURRENT")

Dim rng As Excel.Range
Set rng = ws.Range("C4:Q9996")
rng.NumberFormat = "0.00%"

Open in new window

For cells coloring, this can be done with a conditional formatting:

Dim fc As Excel.FormatCondition
Set fc = rng.FormatConditions.Add(Type:=xlCellValue, Operator:=xlGreater, Formula1:="0,7")
fc.interior.color = vbGreen

Open in new window

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Skylar

ASKER

thanks very much Neeraj!

Skylar

ASKER

Fabrice
thank you for your help.

Subodh Tiwari (Neeraj)

You're welcome Skylar!

Skylar

ASKER

Neeraj,

how can i put border only in certain column or row.

for example, if i want to put thick border in column 1 and row 3?

Subodh Tiwari (Neeraj)

You may add the following two lines in the code...

'The following line will apply the medium border to the range A2:A&LastRow
sws.Range("A2", sws.Cells(lr, 1)).Borders.Weight = xlMedium
'The following line will apply the medium border to the Row2 starting from A2 to the last column used
sws.Range("A3", sws.Cells(3, lc)).Borders.Weight = xlMedium

Open in new window

Skylar

ASKER

Many thanks Neeraj

Subodh Tiwari (Neeraj)

You're welcome again Skylar!