highlight in yellow if within same month

[shili12]

i want to highlight values in column I in yellow along with values in column Q,R,S,T,U if they fall within the same month.
Power query, conditional formatting solutions are ok too.
sample worksheet is attached too.

 

[AliGW]

Which version of Excel are you using?

 

[AliGW]

CF rule for I2:

=AND($I2<>"",MONTH(I2)=MONTH($I2),YEAR(I2)=YEAR($I2))

Applies to: =$I$2:$I$2000,$Q$2:$U$2000

 

[shili12]

office 365, the other data was retrieved using FILTER, observed your worksheet, on issuance dates its highlighting all cells, instead of leaving them void of colour. Maybe i can filter them out like this, its our person who issued certificate from main portal in feb 2024 but we cant see settlement for the same in statement. however there is possibility of typo errors and account indicated as agents name, too.

 

[shili12]

i tried a VBA solution via copilot, am not sure if its a good idea to post it here, maybe i breach forum rules, let me know, anyway i got the opposite effect !!

 

[p45cal]

Conditional formatting in the attached:






Edit, taking a leaf from @AliGW the CF formula for Q2:U22 could be a little simpler:
=AND($I2<>"",MONTH($I2)=MONTH(Q2),YEAR($I2)=YEAR(Q2))

 

[AliGW]

Did you look at the solution I offered you at all? Your subsequent posts suggest not.

 

[p45cal]

Did you look at the solution I offered you at all? Your subsequent posts suggest not.

Perhaps he did:

on issuance dates its highlighting all cells

 

[Marc L]

According to the initial post attachment without any CF a VBA demonstration to paste only to the worksheet module :​

Sub Demo1()
  Const F = "IF(ISNUMBER(Q#:U#),IF(MONTH(Q#:U#)=MONTH(I#),ADDRESS(#,COLUMN(Q#:U#))))"
    Dim L&, R&, V
        L = UsedRange.Rows.Count
        Application.ScreenUpdating = False
        Range("I2:I" & L & ",Q2:U" & L).Interior.ColorIndex = xlNone
    For R = 2 To L
        V = Filter(Evaluate(Replace(F, "#", R)), False, False)
        If UBound(V) > -1 Then Range("I" & R & "," & Join(V, ",")).Interior.ColorIndex = 35
    Next
        Application.ScreenUpdating = True
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[shili12]

Did you look at the solution I offered you at all? Your subsequent posts suggest not.

yes, i did, i tried it out and even sent you a screenshot of how i would circumvent, as colour was reflected in all cells, none excepting. see #4
I even carefully saved the file, as its bound to save me hours and hours of work thru visual checking, which may not be possible with larger files.

 

[Marc L]

which may not be possible with larger files.

With such files avoid CF …​

​

 

[shili12]

Thanks to @Marc L , i just made a slight modification for colour =6.
I am indeed grateful

 

[Marc L]

Typo : should be 36 rather than 35 (or 6 for a brighter yellow or .Interior.Color = vbYellow) …​

​