Carim Thanks for the assist. Not sure why Vlookup did not the opposite direction and again appreciate the help.
I have to admit i may have made this complicated but i honestly do not see my error.
I have a Cell Validation that selects an name on sheet TEST1 "F6". Works FIne i get the list of names from sheet TEST2 column "F".
The formula in Cell validation "F6" is =OFFSET(test2!$F$2,0,0,COUNTA(test2!$F$1:$AF$10)).
My desire is to get The numerical value using =VLOOKUP(F6,test2!A2:F10,1,0) looking in test2.
I have and 2 examples and the AS IS works by Cell Validation and looking for the numerical value and adding name but i now have a need to work project in opposite fashion.
Always appreciative for help
In this case its a time between two dates. Every year i have to change "9/1/2022" to the following year so i was desiring to ref a cell that is is changed so i do not have to do so on multiple pages.
Attached is an example it also clears on the page i do not desire. Thanks
Nice!!!! thanks very much
royuk thanks i ran the second code and got an error compile error and it highlights ".Count" portion of the code with the following message
for each may only iterate over a collection object or an array
I have a rather large workbook about 25 sheets. I was attempting to write a code that clears fill color P2:P10000 on all sheets minus sheet1. I have recorded macro and i can do each individual sheet but was attempting to simplify.
1: Sheet 1 is named dashboard and do not need to do anything on this sheet all other sheets i need to clear fill color in columns P2:P10000 (end of sheet) on all subsequent sheets
Thanks i did not catch that. Thank you very much. I have a lot to learn as it pertains to using Resize and LBound and Ubound
Thanks i shortened for ease and may have made a mistake
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. ThanksCode
- Sub colorCells()
- Application.ScreenUpdating = False
- Dim i As Long, ii As Long, ws As Worksheet, vDate1 As Variant, vDate2 As Variant, srcWS As Worksheet
- Set srcWS = Sheets("dashboard")
- For Each ws In Sheets
- With ws
- vDate1 = srcWS.Range("R3", srcWS.Range("R" & srcWS.Rows.Count).End(xlUp)).Resize(, 4).Value
- vDate2 = .Range("U2", .Range("U" & .Rows.Count).End(xlUp)).Value
- For i = LBound(vDate1) To UBound(vDate1)
- For ii = LBound(vDate2) To UBound(vDate2)
- If Trim(vDate1(i, 1)) = Trim(vDate2(ii, 1)) Then
- .Range("P" & ii + 1).Interior.Color = srcWS.Range("U" & i + 2).Interior.Color
- End If
- Next ii
- Next i
- End With
- Next ws
- Application.ScreenUpdating = True
- End Sub
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
Perfect and wish i would have explained better. Much appreciated
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
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.
Mumps I am still getting errors on months oct/nov/dec are not reflecting the correct Fiscal year. These are unfortunately are the months that started this project. Again really appreciate the assist.