Excel formatting query

  Neill 19:12 01 Sep 06
Locked

I want a range of cells in a row to be shaded blue if one of the cells says O/S.
For example when O/S is typed in B5 and cells B1:B5 will be shaded blue.
This will also happen on the cells in the other rows where O/S is typed in C5 so C1:C5 goes blue and so on with D5, E5 etc
I've explored Conditional Formatting without success. Perhaps a simple macro is the answer but they are beyond me!
Thanks

  VoG II 19:18 01 Sep 06

Use Conditional Formatting using the Formuls Is option. The formula you want is

=OR(B1="O/S", B2="O/S", B3="O/S", B4 = "O/S", B5 = "O/S")

enter this in B1 then use the Format Painter (the 'paintbrush' toolbar button) to apply to all of the cells in the range.

  Noldi 19:23 01 Sep 06

If you enter =COUNTIF(B2:B6,"O/S")in cell B1 then format the cells =$B$1=1

Noldi

  Noldi 19:33 01 Sep 06

Sorry Format should have been Formula is =$B$1>0

Noldi

  Neill 19:45 01 Sep 06

Struggling here but just realised I have given you both completely the wrong information, so sorry. I'll try again:

I want a range of cells in a row to be shaded blue if one of the cells says O/S.
For example when O/S is typed in B5 and cells in the row A5:F5 will be shaded blue.
This will also happen on the cells in the other rows so where O/S is typed in B6, A6:F6 goes blue and so on with B7, B8 etc
Hope you are still there.

  VoG II 20:22 01 Sep 06

Well you should be able to adapt either of the suggested solutions to do this.

  VoG II 20:51 01 Sep 06

Or try this. Right click the sheet tab, View Code. Paste in the following

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = "O/S" Then
Target.EntireRow.Cells.Interior.ColorIndex = 5
End If
End Sub

Close the code window. This will only work with new entries of O/S.

  Neill 21:20 01 Sep 06

Dear VoG
Did try and adapt your first solution with some success. However if I formatted cells A1 to H1 with =OR(A1="O/S", B1="O/S", c1="O/S", D1 = "O/S", E1 = "O/S", F1 = "O/S", G1 = "O/S", H1 = "O/S") then typed O/S in E1 then only A1 to E1 where formatted blue even though A1:H1 were formatted using Format Painter as you suggested. So this would work if the cell that O/S is typed in is the final cell in the row, H1 is this case.
The View Code does the whole row as you know so I'll stick with the first solution. Thanks

  VoG II 21:30 01 Sep 06

If you only want to shade columns A to H then

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = "O/S" Then
Range(Cells(Target.Row, 1), Cells(Target.Row, 8)).Interior.ColorIndex = 28
End If
End Sub

which also uses a different shade of blue.

This thread is now locked and can not be replied to.

Surface Pro 5 News - release date, UK price, features, specs

Microsoft Surface Studio hands-on review: the iMac killer is here

Best Mac antivirus 2017