Colour cells if condition met

[Sliepner]

I have a rota in which I would like to have all the cells in a row with text/numbers in them highlight blue if there is an N in eg column A. i am trying to avoid conditional formatting if possible.
The image is a snip from a Rota.
The N in the 1st column signifies night shift
1 signals they are here
M* mobilising
D* departing

Is this possible, and if so, what is the best way to achieve. The rota has over 800 rows and covers a year, 1 column per day. We have previously used conditional formatting, but as the rota is used pretty much all day, every day, the formatting inevitably splits and grows exponentially making the spreadsheet slow.

Any advice gratefully received.

 

[kweaver]

Why is the column with the 1s shaded when you said you wanted rows shaded when the A column has an "N" ?

I think if you don't use conditional formatting you'll have to use VBA.

 

[kweaver]

Maybe something like this?

Sub ShadeRows()
Dim RowCount As Long, ColCount As Long, i As Long, rng As String

Application.ActiveSheet.UsedRange
RowCount = Worksheets("ROTA").UsedRange.Rows.Count
ColCount = Worksheets("ROTA").UsedRange.Columns.Count

For i = 1 To RowCount
 If Cells(i, "A") = "N" Then
 rng = Range(Cells(i, "A").Address, Cells(i, ColCount).Address).Address
 Range(rng).Interior.ColorIndex = 34
 Else
 End If
Next

End Sub

 

[Sliepner]

Ah, yes the column which is orange is todays date

Why is the column with the 1s shaded when you said you wanted rows shaded when the A column has an "N" ?

I think if you don't use conditional formatting you'll have to use VBA.

Ah yes, sorry I forgot to add that the orange column is todays date