conditional Format utilizing a Vlookup

  • I am working a rather large project...meaning 30 plus sheets. I can use conditional formats for 5 separate variables and was curious if there is an easier or smarter way to approach this project. So i was working A vlookup but am hung up on not getting the proper results:


    1) in Column A i have Date that i need to figure out the FY and I do so in Column B. I have a color legend FY22= orange FIll color

    2) Can i use a Vlookup and Change the fill color in Column "A" to correspond with the color legend provided in column I.


    My thinking is I will have to modify new fiscal year because for example: FY23 next year will be orange and 2027 will be added and be purple. and i will have to modify on all 30+ sheets. All sheets are the same columns just different data. So my desire is not to modify all sheets just update the FY range.

  • If all the sheets contain the color legend, this macro should work. If the color legend is only on one sheet, what is the name of that sheet?

    With this macro you don't need column B.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Try:

    With this macro you don't need column B.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Try:

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • I am still getting errors on months oct/nov/dec are not reflecting the correct Fiscal year

    I'm afraid that I don't follow. The macro bases the coloring on the year in each cell in column A. I'm not sure of how the months apply. Please explain in detail how the code is not working for you referring to specific cells, rows, columns and sheets using a few examples from your data.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • I am attempting to color code the Fiscal Years and for this project it start in October. The current macro correctly annotates the year and not FY. This project years go OCT to OCT. I created column "B" as i thought it would be easier to look in "B" and fill color accordingly in "A". Apologies if i did not properly clarify.

  • I still don't follow. Column B, as far as I can see, simply contains the year from the cell to the left in column A so I really don't see the need for column B since I can extract the year from column A. Please clarify. The macro loops through the years in column F of the dashboard and finds all the cells in column A of each sheet that contain each year and then colors the corresponding cells in column A with the color from column I in the dashboard. I am simply referring to the years not the months. So I still need deatiled clarification on how the months apply. Please explain in detail how the months apply referring to specific cells, rows, columns and sheets using a few examples from your data.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Column "B" reflects year+month and references "C1" to dictate what month to begin with and in this case 10 (OCT).

    Good example: A6 has a calendar Year date of 20221001 and its Fiscal Year is 2023 because the FY Starts OCT for budgeting reasons.


    Quote

    The macro loops through the years in column F of the dashboard and finds all the cells in column A of each sheet that contain each year and then colors the corresponding cells in column A with the color from column I in the dashboard.

    I was thinking if we loop through column "F" of the dashboard and find in Column "B" and color "A" with the color from the dashboard. Its convoluted but i was unable to figure out how to do any other way with existing dates. Thanks

  • See if this works for you.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • I think i misunderstand...If i changed from "F3" to "R3" do I need to change the Resize Value? I thought it was pretty straight forward but i didnt understand as well as I thought. The Code works as written but needed to tighen a sheet so just trying to understand the resize portion of the code



    Code
    1. vDate1 = srcWS.Range("F3", srcWS.Range("F" & srcWS.Rows.Count).End(xlUp)).Resize(, 4).Value
    2. change to
    3. vDate1 = srcWS.Range("R3", srcWS.Range("R" & srcWS.Rows.Count).End(xlUp)).Resize(, 4).Value
  • The resize portion simply represents the number of columns in your color chart in the dashboard so the "4" means there are 4 columns (F:I). If the color chart now starts in column R, the resize number doesn't need to change as long as the color chart still contains 4 four columns which would now be R:U. Does this make sense?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Mumps it does as i thought. Once i transitioned my example to my workbook I had to shift some columns and it works but i get a run-time error Type Mismatch highlighted below. Any ideas. The problem arose when i moved from "F3" to "R3". I did get the bug but it coded perfectly. Thanks


  • Please attach a copy of the file (de-sensitized if necessary) that is producing the error.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • The code has to exclude the "dashboard" sheet.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.