Dear Prince,
Thank you very much for reading our article.
According to your query, 1st you wanted to know about a formula to create a drop-down list that will be used to select different types of leaves. You will get that in Step 3 of this article. In our Excel file, we selected leave using the drop-down list in Record sheet. Also when you move to any month leave information will be based on the Record sheet. So, information will not move from one month to another. Try this and hope you will get the solution.
Otherwise, send your Excel file with what you want to get and we will try to provide a solution. You can mail us at [email protected].
Thanks
Joyanta Mitra
ExcelDemy

This code will solve your problem. The code has a condition checking values blank or not.

Sub Duplicates_Dif_Colors()
Dim RG As Range
Dim TT As String
Dim CL As Range
Dim CR As String
Dim CP As Range
Dim CD As Long
Dim Cltn As Collection
Dim J As Long

On Error Resume Next

If ActiveWindow.RangeSelection.Count > 1 Then
TT = ActiveWindow.RangeSelection.AddressLocal
Else
TT = ActiveSheet.UsedRange.AddressLocal
End If

Set RG = Application.InputBox("Select range of data with duplicates:", "Duplicate values with Colors", TT, , , , , 8)
If RG Is Nothing Then Exit Sub

CD = 2
Set Cltn = New Collection

For Each CL In RG
If CL.Value <> "" Then ' check if cell is not blank
On Error Resume Next
Cltn.Add CL, CL.Text
If Err.Number = 457 Then
CD = CD + 1
Set CP = Cltn(CL.Text)
If CP.Interior.ColorIndex = xlNone Then CP.Interior.ColorIndex = CD
CL.Interior.ColorIndex = CP.Interior.ColorIndex
ElseIf Err.Number = 9 Then
MsgBox "Found excessive duplicates", vbCritical, "Duplicates with Colors"
Exit Sub
End If
On Error GoTo 0
End If
Next
End Sub

Output:

First, you have to create different power queries for each worksheet for other tickers and copy the same code. Otherwise, it triggers the first. Then for 10 different tickers,the code is

let
    TickerList = {"AAPL", "GOOG", "MSFT", "AMZN", "FB", "TSLA", "NVDA", "JPM", "BAC", "V"},
    StartDate = Excel.CurrentWorkbook(){[Name="StartDate"]}[Content]{0}[Column1],
    EndDate = Excel.CurrentWorkbook(){[Name="EndDate"]}[Content]{0}[Column1],
    #"Loop Through Tickers" = List.Transform(TickerList, each
        let
            Ticker = _,
            Source = Csv.Document(Web.Contents("https://query1.finance.yahoo.com/v7/finance/download/"&Ticker&"?period1"&StartDate&"&period2"&EndDate),[Delimiter=",", Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None]),
            #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
            #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Open", type number}, {"High", type number}, {"Low", type number}, {"Close", type number}, {"Adj Close", type number}, {"Volume", Int64.Type}})
        in
            #"Changed Type"
    ),
    #"Loop Through Tickers1" = #"Loop Through Tickers"{8}
in
    #"Loop Through Tickers1"

this code creates 10 tables for 10 different tickers. You have to select one table according to Ticker and hover over the query in Workbook Query Section and press View in Worksheet. You will get the table have to create 10 sheets separately.