I spent few hours finding this Online with NO luck. Hopefully someone here can help me.
I have Table with 17 columns and 1000+ rows
I am trying to set conditional formatting Based On Column Heading as Target. Formula Changes Based On Target Heading
A Macro sorts the table based on heading.
Conditional Formatting Formula Puts Borders On Rows by comparing cell value to cell value below in the the Target column.
My conditional formatting formula is working fine but it's too long and may increase.
Conditional Formatting Formula:
=IF(SortBy_Code=2,$E2=OFFSET($E2,1,0),IF(SortBy_Code=3,$F2=OFFSET($F2,1,0),IF(SortBy_Code=4,$G2=OFFSET($G2,1,0),IF(SortBy_Code=5,$H2=OFFSET($H2,1,0),IF(SortBy_Code=6,$I2=OFFSET($I2,1,0),IF(SortBy_Code=7,$J2=OFFSET($J2,1,0),IF(SortBy_Code=8,$K2=OFFSET($K2,1,0),IF(SortBy_Code=9,$M2=OFFSET($M2,1,0),IF(SortBy_Code=10,$O2=OFFSET($O2,1,0),IF(SortBy_Code=11,$P2=OFFSET($P2,1,0),IF(SortBy_Code=12,$Q2=OFFSET($Q2,1,0),IF(SortBy_Code=13,$S2=OFFSET($S2,1,0),IF(SortBy_Code=14,$U2=OFFSET($U2,1,0),IF(SortBy_Code=15,$W2=OFFSET($W2,1,0),IF(SortBy_Code=16,$X2=OFFSET($X2,1,0),IF(SortBy_Code=17,$Z2=OFFSET($Z2,1,0)))))))))))))))))
Broken down for easy reading
=IF(SortBy_Code=0,$C2=$C2,
IF(SortBy_Code=1,$C2=OFFSET($C2,1,0),
IF(SortBy_Code=2,$E2=OFFSET($E2,1,0),
IF(SortBy_Code=3,$F2=OFFSET($F2,1,0),
IF(SortBy_Code=4,$G2=OFFSET($G2,1,0),
IF(SortBy_Code=5,$H2=OFFSET($H2,1,0),
IF(SortBy_Code=6,$I2=OFFSET($I2,1,0),
IF(SortBy_Code=7,$J2=OFFSET($J2,1,0),
IF(SortBy_Code=8,$K2=OFFSET($K2,1,0),
IF(SortBy_Code=9,$M2=OFFSET($M2,1,0),
IF(SortBy_Code=10,$O2=OFFSET($O2,1,0),
IF(SortBy_Code=11,$P2=OFFSET($P2,1,0),
IF(SortBy_Code=12,$Q2=OFFSET($Q2,1,0),
IF(SortBy_Code=13,$S2=OFFSET($S2,1,0),
IF(SortBy_Code=14,$U2=OFFSET($U2,1,0),
IF(SortBy_Code=15,$W2=OFFSET($W2,1,0),
IF(SortBy_Code=16,$X2=OFFSET($X2,1,0),
IF(SortBy_Code=17,$Z2=OFFSET($Z2,1,0)
))))))))))))))))))
I know I can create 17 individual condition formatting but that would be to much to track and change and it may increase
I tried multiple ways to work around by using named range but no luck.
I Also tried the following User Defined Function but it doesn't do anything.
Function Show_ThinLine(SortBy_Code As integer, ThisCell As Range) As Boolean
If SortBy_Code=3 then ' which is True
If ThisCell.Value = ThisCell.Offset(1, 0).Value Then Show_ThinLine = True
end if
End Function
Range selected: A2:Q1000 Whole Table without header and total rows
Set Conditional Formattng Forumula to: =Show_ThinLine(3,$A2)=True , I tried A2, $A$2, Nothing would work
Range $A2 could be anyone of the 2nd Row or 1st Table Row
Forumula will work if put in cell but not in Conditional Formatting
Thank You Anyone Who Tries
Bookmarks