How to turn formula into VBA COnditional Format

[liemaj]

Hi all,

I'm in a search of a VBA guru.

I'd like to turn the following formula into a VBA Conditional Format code:

=IF((SUMPRODUCT(COUNTIFS(INDIRECT("'"&Table1[SheetNames]&"'!$D:$D"),$D16,INDIRECT("'"&Table1[SheetNames]&"'!F:F"),"Y")))-IF(VLOOKUP(D16,D16:O16,3,FALSE)="Y",1,0)>0,TRUE,FALSE)

[SheetNames] = sheet names in workbook

I have a table that contains values in one column and either Y/N in another column and an insert script in the third.. If two rows contain duplicate data AND both have a "Y" value in the next column, then a user changes one of them to N, then the column containing the insert script will highlight in red. Example:

A1 = LOREM IPSUM, B1 = Y, C1 = *insert script* (generated if value in column B is either Y/N), D1 = FALSE
A2 = LOREM IPSUM, B2 = N, C2 = *insert script*, D2 = TRUE <-- (written conditional format so that Cell C2 will highlight)

Can someone help me write the above formula in the form of a VBA code so that it achieves my desired outcome?

If any of the above is not clear, please let me know.

Thanks

 

[James006]

Hi,

Turn on your macro recorder ... and input your formula ...

You will get an initial ' VBA translation ' ...

Hope this will help