• Status: Solved
  • Priority: High
  • Security: Private
  • Views: 45
  • Last Modified:

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
0
Skylar-Barlow
Asked:
Skylar-Barlow
1 Solution
 
NorieVBA ExpertCommented:
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?
0
 
Fabrice LambertFabrice LambertCommented:
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

1
 
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.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=C4>0.7"
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    .FormatConditions(1).Interior.Color = RGB(146, 208, 80)
        
    .FormatConditions.Add Type:=xlExpression, Formula1:="=C4<0.1"
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    .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

1
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Skylar-BarlowAuthor Commented:
thanks very much Neeraj!
0
 
Skylar-BarlowAuthor Commented:
Fabrice  
thank you for your help.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Skylar!
1
 
Skylar-BarlowAuthor Commented:
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?
0
 
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

1
 
Skylar-BarlowAuthor Commented:
Many thanks Neeraj
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome again Skylar!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now