BlogFor BusinessTalk to a counsellor
CoursesIIT ProgramsPlacementsLog in
BlogFor Business
Talk to a counsellor
Challenge

Project 2 - Create a report using PowerQuery, Macro VBA, List Functions and Data Validation functions for Data Reporting of Supply Chain Management companies

PPadala Vishweshwari24 Jan 2023
  • Range("Applicant_Data[[#All],[AMT_INCOME_TOTAL]]").Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
    Formula1:="=540000"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
    .Color = -16754788
    .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 10284031
    .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
    Formula1:="=220000"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
    .Color = -16752384
    .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 13561798
    .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    ActiveWindow.SmallScroll Down:=-115
    ActiveWindow.ScrollRow = 307484
    ActiveWindow.ScrollRow = 307033
    ActiveWindow.ScrollRow = 304782
    ActiveWindow.ScrollRow = 300731
    ActiveWindow.ScrollRow = 251659
    ActiveWindow.ScrollRow = 143613
    ActiveWindow.ScrollRow = 118852
    ActiveWindow.ScrollRow = 90490
    ActiveWindow.ScrollRow = 1
    Range("E3").Select
    ActiveWindow.FreezePanes = True
    Range("G9").Select
    End Sub
  • Create VBA Code to convert selected data into pdf from applicant data
  • Sub PrintSelectionToPDF()
    Dim ThisRng As Range
    Dim strfile As String
    Dim myfile As Variant
    If Selection.Count = 1 Then
    Set ThisRng = Application.InputBox("Select a range", "Get Range", Type:=8)
    Else
    Set ThisRng = Selection
    End If
    'Prompt for save location
    strfile = "Selection" & "_" _
    & Format(Now(), "yyyymmdd_hhmmss") _
    & ".pdf"
    strfile = ThisWorkbook.Path & "\" & strfile
    myfile = Application.GetSaveAsFilename _
    (InitialFileName:=strfile, _
    FileFilter:="PDF Files (*.pdf), *.pdf", _
    Title:="Select Folder and File Name to Save as PDF")
    If myfile <> "False" Then 'save as PDF
    ThisRng.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    myfile, Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=True
    Else
    MsgBox "No File Selected. PDF will not be saved", vbOKOnly, "No File Selected"
    End If
    End Sub
  • Check Spelling mistakes in the workbook using VBA Code

  • Public Sub HighlightSpellingMistakes()

    Dim wd As Range

    For Each wd In ActiveSheet.UsedRange

    If Not Application.CheckSpelling(word:=wd.Text) Then

    wd.Font.Color = vbRed

    wd.Interior.Color = vbYellow

    End If

    Next wd

    End Sub

Your portfolio, next

Ship work like this
in your own course.

Talk to a career counsellor

Free 20-minute call. We'll map the right course to where you want to work.

By submitting you agree to our Terms and Privacy Policy.