Cell shading question...

[djroppolo]

Suppose I am entering data into a simple spreadsheet, starting at cell A7 and ending at I7. Could I have the entire row from A7-I7 shaded a certain color depending on the value of I7? Also, how would you modify that to work on every row of the spreadsheet?

Thanks in advance

 

[HalfAce]

Hello djroppolo, welcome aboard.

The question still remains of how many girls' names you have.
You're limited to only 4 conditions (4 different names) with the conditional format if you count your default format, without a bit of fancy footwork.

Is this going to be an issue?

Dan

 

[HalfAce]

OK, saw the last replies after my last, so here's an example of an On Change event that Nimrod mentioned.
The code goes into the sheet module.
It will work when you make / change an entry in column I from row 7 down.
I tried to explain pretty much what it's doing, but post back if you still need a hand with it.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ColRng As Range, TgtRw As Long

'SET THE RANGE OF INTEREST AS COLUMN I FROM ROW 7 DOWN
Set ColRng = Range("I7:I65536")

'DEFINE THE VARIABLE 'TgtRw' AS THE ROW THE CHANGE OCCURRED IN
TgtRw = Target.Row

'IF THE CHANGE DIDN'T HAPPEN IN THE RANGE OF INTEREST, DO NOTHING
If Intersect(Target, ColRng) Is Nothing Then Exit Sub

'IF THE CHANGE WAS TO MORE THAN ONE CELL, DO NOTHING
If Target.Count > 1 Then Exit Sub

Select Case Target.Value
    Case "Jennifer"
        Range(Cells(TgtRw, 1), Cells(TgtRw, 9)).Interior.ColorIndex = 44

    Case "Susan"
        Range(Cells(TgtRw, 1), Cells(TgtRw, 9)).Interior.ColorIndex = 45

    Case "Renea"
        Range(Cells(TgtRw, 1), Cells(TgtRw, 9)).Interior.ColorIndex = 46

'Continue with more of your criteria

'REMOVE COLOR IF VALUE IS NONE OF THE ABOVE
    Case Else
        Range(Cells(TgtRw, 1), Cells(TgtRw, 9)).Interior.ColorIndex = xlNone

End Select

End Sub

Hope it helps,
Dan

 

[djroppolo]

1. Is there a wildcard for the row#???

..... What are you trying to achieve here ??

The row number will start at 7 but increase by 1 everytime i start a new row. Just wondering how to add 1 to that number for every new row.

 

[djroppolo]

OK, saw the last replies after my last, so here's an example of an On Change event that Nimrod mentioned.
The code goes into the sheet module.
It will work when you make / change an entry in column I from row 7 down.
I tried to explain pretty much what it's doing, but post back if you still need a hand with it.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ColRng As Range, TgtRw As Long

'SET THE RANGE OF INTEREST AS COLUMN I FROM ROW 7 DOWN
Set ColRng = Range("I7:I65536")

'DEFINE THE VARIABLE 'TgtRw' AS THE ROW THE CHANGE OCCURRED IN
TgtRw = Target.Row

'IF THE CHANGE DIDN'T HAPPEN IN THE RANGE OF INTEREST, DO NOTHING
If Intersect(Target, ColRng) Is Nothing Then Exit Sub

'IF THE CHANGE WAS TO MORE THAN ONE CELL, DO NOTHING
If Target.Count > 1 Then Exit Sub

Select Case Target.Value
    Case "Jennifer"
        Range(Cells(TgtRw, 1), Cells(TgtRw, 9)).Interior.ColorIndex = 44

    Case "Susan"
        Range(Cells(TgtRw, 1), Cells(TgtRw, 9)).Interior.ColorIndex = 45

    Case "Renea"
        Range(Cells(TgtRw, 1), Cells(TgtRw, 9)).Interior.ColorIndex = 46

'Continue with more of your criteria

'REMOVE COLOR IF VALUE IS NONE OF THE ABOVE
    Case Else
        Range(Cells(TgtRw, 1), Cells(TgtRw, 9)).Interior.ColorIndex = xlNone

End Select

End Sub

Hope it helps,
Dan

Thanks, that seems like what im looking for except Im not sure where to plug it in at

 

[just_jon]

Hit Alt + F11 to open the VB Editor.

On the left of the screen you should see a project list. Find your workbook name, then within that workbook select the sheetname you want to monitor.

The code goes in the white space in the center/right of your screen. Just copy/paste.

 

[HalfAce]

OK, Right click the sheet (name) tab of the sheet you want to enter the names in and choose View code.
This opens up the VB Editor and puts you in the code pane (or the sheet's code module) for that worksheet.
Copy the code from the post above and paste it in the white area that is the sheet code module.
Press AltQ to quit the VB Editor and get back to your sheet.
Enter Jennifer (or Susan or Renea) in column I in any row below row6.
They will each have their own color of orange.
If you clear the name(s) the color(s) will disappear.
You can change the colors easily enough by changing the numbers for the color index in the case statements. You can also add more names for more case statements.

Note though that the names must be spelled correctly and in the proper case. If you don't want the code to be case sensitive, you can add this line above the beginning of the code. (Above the line 'Private Sub Worksheet_Change(ByVal Target As Range)')

Option Compare Text

Then it will just test for the proper string, not the text case being used.

It might be worth checking into using Data Valiation lists in column I so you can have dropdown boxes to select the names from instead of having to manually enter them. (That way spelling is not an issue...)

Does this help any?
Dan