Conditional formatting - 3 more rows

[madocar]

Hi
I would like to ask you if there is a way to background entire row if there is a value 16 in column B + 3 rows upper.
So if the value of cell B12 is 16 all 12th row will be green background and 9th, 10th and 11th as well by using conditional formatting.
Example file in attachment.

Thanks for any help

 

[Khalid NGO]

Hi,

I have added one more condition with:
=OR($B2=16,$B3=16,$B4=16)

See if this is okay.

Regards,

 

[madocar]

That look's good but sometimes I need to background 3 rows upper, any other time 5 rows upper. Is it possible to background X rows upper according to value in cell D5.
If I put there 5 I will background 5 more rows upper, if I put 3 in the cell D1 I will background 3 more rows upper ....
Thanks a lot

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan

 

[madocar]

Hi ,

See if this is OK.

Narayan

Hi Narayan,

there is probably something wrong with excel file:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>error062560_02.xml</logFileName><summary>V sĂşbore C:\Users\janis\Downloads\chandoo - Conditional formatting - 3 more rows(1).xlsx sa zistili chyby</summary><additionalInfo><info>Program Excel dokonÄŤil overovanie Ăşrovne sĂşboru a opravu. Je moĹľnĂ©, Ĺľe niektorĂ© ÄŤasti tohto zošita boli opravenĂ© alebo zahodenĂ©.</info></additionalInfo><removedFeatures summary="Zoznam odstránenĂ˝ch funkciĂ:"><removedFeature>Odstránená funkcia: PodmienenĂ© formátovanie z ÄŤasti /xl/worksheets/sheet1.xml</removedFeature></removedFeatures></recoveryLog>

 

[NARAYANK991]

Hi ,

I do not know what the problem is , so I am posting the CF formula :

=IF(OR(N(OFFSET($B2,ROW(INDEX($A:$A,1):INDEX($A:$A,IF($D$5>=0,$D$5,0)+1))-1,0))=16),1,0)

Select your data range , say rows 2 through 18 , and enter this formula.

Narayan

 

[Khalid NGO]

=IF(OR(N(OFFSET($B2,ROW(INDEX($A:$A,1):INDEX($A:$A,IF($D$5>=0,$D$5,0)+1))-1,0))=16),1,0)

Beautiful formula Sir
Still figuring it out how its working.

 

[madocar]

Beautiful formula Sir
Still figuring it out how its working.

Yes It is.
It is not the first time when Narayank991 helped me a lot
Thanks a lot