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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:

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 LambertFabrice LambertCommented:

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

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may try something like this...
Sub ApplyFormatting()
Dim sws As Worksheet
Dim lr As Long, lc As Long
Dim Rng As Range
Set sws = Sheets("Current")
lr = sws.UsedRange.Rows.Count
lc = sws.UsedRange.Columns.Count

Set Rng = sws.Range("C4", sws.Cells(lr, lc))

With sws.Range("B1", sws.Cells(1, lc))
    .Font.Superscript = True
    .Interior.Color = RGB(220, 230, 241)
End With

sws.Range("B3", sws.Cells(3, lc)).Font.Color = vbRed

With Rng
    .NumberFormat = "0%"
    .FormatConditions.Add Type:=xlExpression, Formula1:="=C4>0.7"
    .FormatConditions(1).Interior.Color = RGB(146, 208, 80)
    .FormatConditions.Add Type:=xlExpression, Formula1:="=C4<0.1"
    .FormatConditions(1).Interior.Color = RGB(255, 192, 0)
End With

End Sub

Open in new window

Then you may call this sub-routine in your main macro like this...

Sub YourMainMacro()
Call ApplyFormatting
End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Skylar-BarlowAuthor Commented:
thanks very much Neeraj!
Skylar-BarlowAuthor Commented:
thank you for your help.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Skylar!
Skylar-BarlowAuthor Commented:

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)Excel & VBA ExpertCommented:
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-BarlowAuthor Commented:
Many thanks Neeraj
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome again Skylar!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.