SOLVED

Highlighting multiple rows with conditional formatting and copying with relative references

Copper Contributor

I am trying to create a file to track contracts, and would like to highlight 3 rows tan, green, or red based on whether the contract is Paid, Active, or Expired.  I can get the 3 rows ($A$1:$H$3) to highlight correctly but only when I use an absolute reference ($H$2="PAID").  If I try using only $H2 the colors get jumbled and do not highlight across the 3 rows correctly.  If I keep the absolute reference and then try to copy/paste those rows (to add a new contract) the conditional formatting will move the Applies to area correctly, but still keeps the absolute reference as $H$2.

 

Does anyone have any suggestions?  I suppose I could change it to only highlight the top row if highlighting multiple rows is the problem, but I prefer the look of the 3 rows highlighted.

 

Thanks for any assistance!

2 Replies
best response confirmed by stuckonweb (Copper Contributor)
Solution

@stuckonweb The only way I could get this to work was to add the status in each of the 3 header rows in  column I and reference the CF rules to $I1. Then you can copy the formatting as you would like to. Have hidden column I in the attached workbook.

@Riny_van_EekelenThat works great!  Thank you so much - you saved my sanity! : )

1 best response

Accepted Solutions
best response confirmed by stuckonweb (Copper Contributor)
Solution

@stuckonweb The only way I could get this to work was to add the status in each of the 3 header rows in  column I and reference the CF rules to $I1. Then you can copy the formatting as you would like to. Have hidden column I in the attached workbook.

View solution in original post