Dynamic Shading of cell based on a value in another cell (row number)

# Dynamic Shading of cell based on a value in another cell (row number)

• Wednesday, November 21, 2012 8:04 PM

I would like to shade(gray) the values in # Store BOM based on when the rownumber is less then the rownumber of the current month.

So the result would be Month Feb-Oct would be gray while Nov-Jan would remain white.

### All Replies

• Wednesday, November 21, 2012 8:49 PM
Moderator

Hi There

Thanks for your posting. Can you please let us know what type of report you are using? Are you using Matrix report? I mean How you are displaying the information the line above # Store BOM .

What does the rownumber line displaying?

Many thanks

Syed

• Wednesday, November 21, 2012 8:58 PM

yes is a matrix report.

rownumber = the result of using the RowNumber() on the group. So it will always be 1 to 12.

• Wednesday, November 21, 2012 9:16 PM
Moderator

Hi There

Thanks for your posting. Let say you have column group with month so you can write expression for the textbox # Store BOM fill colour like this

=iif(Fields!month.Value>1,"SeaGreen","Gray")

If you have any question please let me know.

Many thanks

Syed Qazafi Anjum

• Proposed As Answer by Thursday, November 22, 2012 3:09 PM
•
• Wednesday, November 21, 2012 9:26 PM

Hello,

This does not solve my issue as your the "1" in your "iif(Fields!month.Value>1" is constant. I need the shading to occur dynamically based on the current month No. Becasue these are fiscal months and cant use date math as Jan which is the last fiscal month < the current calendar month of Nov. This is way im trying to use a rownumber in some way.

• Edited by Wednesday, November 21, 2012 9:28 PM
•
• Thursday, November 22, 2012 8:43 AM
Moderator

Hello,

What's the data type of the “Month” field? If it is Date type, then please refer to the following expression:

=IIF(DateDiff(“m”,First(Fields!Month.Value), Fields!Month.Value)>=0 and DateDiff(“m”,Fields!Month.Value, now())<0),"Gray","White”)

`=IIF(DateDiff(“m”,First(Fields!Month.Value), Fields!Month.Value)>=0 and DateDiff(“m”,Fields!Month.Value, now())<0),"Gray","White")`

Regards,
Fanny Liu

Fanny Liu

TechNet Community Support

• Thursday, November 22, 2012 3:04 PM

Hello Fanny,

No its not its just text.

Thats ok I codes the logic in the dataset.

Thanks to everyone for responding.

• Marked As Answer by Thursday, November 22, 2012 3:05 PM
•