Auto Formatting of rows

[spock1971]

Guys

I'm trying to automate a report in excel. The company like a thin grey row to seperate a range of rows that vary weekly depending on a data file being uploaded. Basically, the grey row needs to move automatically.

I've set up formulae to set a cell in column B to say 'Grey' when the bar needs to appear. Now I need excel to recognise which row contains the word Grey and format that row. Remembering there will be upto 13 grey rows required.

Any ideas?

 

[shades]

One way to do it, but may need a little update each week. Select the entire region, then Format > Conditional Formatting. On the left choose "Formula is" from the dropdown. Then on the right put this formula on the right

=$B2="Grey"

Then choose the format appropriate.

Another way to do this is to use VBA, with no need to make weekly adjustments. Are you interested? I have some code that does something similar.
________
NSR500

 

[spock1971]

Tried the formula is and it didn't work - only in the actual cell b2 not the row. I suppose I could conditional format each cell. Blimey.

Yes, code would be nice - would that then need a run macro command. I thought about macro's, but the row could move weekly so I'd need to do a global edit clear format and re-code.

Cheers

D

 

[shades]

I have a "CFControl" worksheet that is hidden, which contains the the named range: rngcolors defined this way:

=CFControl!$A$2:$B$87

With the worksheet active that you want the formatting, then right-click the tab and choose "View Code". This brings up the VBE window, with the module on the right. Paste the following code into the window.

The code then uses that hidden worksheet as a reference to know which color to use. I went this route because as you can see I had 86 possible conditions. This code automatically handles all 86 automatically adding the color. If I need to change the colors, I do so on the hidden worksheet ("CFControl") rather than doing anything with the code. Adjust your range on the active worksheet as needed

Private Sub Worksheet_Change(ByVal Target As Range)
' Conditional Formatting for more than 3 conditions
    Dim rng As Range
    ' Target is a range::therefore,it can be more than one cell
    ' For example,,someone could delete the contents of a range,
    ' or someone could enter an array..
    Set rng = Intersect(Target, Range("P2:Y72"))
    If rng Is Nothing Then
        Exit Sub
    Else
        Dim cl As Range
        For Each cl In rng
            On Error Resume Next
            ' ? The preceding line doesn ?t change the cell ?s background
            ' ? color if the cell ?s value is not found in the range
            ' ? that we specified ((rngcolors).
            cl.Font.ColorIndex = _
            Application.WorksheetFunction.VLookup(cl.Value, _
                ThisWorkbook.Sheets("CFControl").Range("rngColors"), 2, False)
            If Err.Number <> 0 Then
                cl.Font.ColorIndex = xlNone
            End If
        Next cl
    End If
End Sub

HTH
________
Glass bongs

 

[shades]

Tried the formula is and it didn't work - only in the actual cell b2 not the row. I suppose I could conditional format each cell. Blimey.

Did you select all the cells in the row before going to Conditional Formatting? Did you have the Dollar sign in front of B? Also, if you have cell $B2 correctly formatted, then you can use the Format Painter to extend it to other cells.
________
Kawasaki kz400

 

[spock1971]

Will give these a go. Thanks.

 

[spock1971]

Call me stupid but I tried the conditional format route again and it's working fine now. Yippee.

Something else you may be able to help me with, in the same report I have conditional formatting changing cell colours to red, amber, or green depending on the text value of the cell. However, those grey row cells don't have any text value assigned but still change to amber.

Any ideas why?

 

[shades]

It depends. Will they change at any time in the future. If not, then select those grey cells that have no values, and go to Format > Conditional Format and then click on the Delete button, and click all three conditions. Then click OK.

If they will change, then you might want to consider the VBA option.
________
Buy vaporgenie