Format cell value changed AND selected columns

[Rx_]

Part 2 of the question

1. This is the base code that bolds a cell value if it changes in a sorted column.
I.E. : values such as Customer Name are sorted, every time a new customer name is in the list - the cell is bolded:

For Each c In ActiveWindow.Selection
If c.Value <> c.Offset(-1, 0).Value Then
c.Font.FontStyle = "Bold"
End If
Next c

2. Now, the customer requested that selected columns are also bolded.
i.E. A changed customer name is bolded from above.
While at the c.Font.FontStyle = "Bold" for a value of C.Value
go over 5 columns and bold the next three cells on the same row.

Had this code somewhere and can't seem to find it.

Jones 1 2 3 4 5 Tall Ok Medium
Jones 1 2 3 4 5 Tall Ok Medium
Jones 1 2 3 4 5 Tall Ok Medium
Kline 1 2 3 4 5 Tall Ok Medium
Kline 1 2 3 4 5 Tall Ok Medium
Kline 1 2 3 4 5 Tall Ok Medium

AND:
While on the subject
Might as well ask about bolding an entire row.
Someone will probably be looking for that answer later too.

 

[chergh]

For your specific question:

For Each c In ActiveWindow.Selection
If c.Value <> c.Offset(-1, 0).Value Then
c.Font.FontStyle = "Bold"
c.parent.range(c.offset(0,6),c.offset(0,8).font.bold = true
End If
Next c

For the entire row

For Each c In ActiveWindow.Selection
If c.Value <> c.Offset(-1, 0).Value Then
c.Font.FontStyle = "Bold"
c.EntireRow.Font.Bold = True
End If
Next c

 

[Rx_]

THANKS!
the parent, always forget about the parent

 

[Brianwarnock]

You need a closing )
c.parent.range(c.offset(0,6),c.offset(0,8)).font.bold = true

I don't think the following line is required when bolding the entire row
c.Font.FontStyle = "Bold"

Brian

 

[Rx_]

Figured out the parens. but could not figure out this:
'c.Parent.Range(c.Offset(0, 6), c.Offset(0, 8)).ThemeColor = xlThemeColorDark1
'c.Parent.Range(c.Offset(0, 6), c.Offset(0, 8)).Font.TintAndShade = -0.249977111

See how I commented out the error generating code.

objXL.Range("B" & intRowPos & ":B" & intMaxRecordCount + (intRowPos)).Select
    ' Row B has well names in order with dates in order
 
    For Each c In objXL.ActiveWindow.Selection
    If c.Value <> c.Offset(-1, 0).Value Then
        c.Font.FontStyle = "Bold"
        c.EntireRow.Font.Bold = True
    Else
        c.Font.ThemeColor = xlThemeColorDark1
        c.Font.TintAndShade = -0.249977111
        c.EntireRow.Font.ThemeColor = xlThemeColorDark1
        c.EntireRow.Font.TintAndShade = -0.249977111
        'c.Parent.Range(c.Offset(0, 6), c.Offset(0, 8)).ThemeColor = xlThemeColorDark1
        'c.Parent.Range(c.Offset(0, 6), c.Offset(0, 8)).Font.TintAndShade = -0.249977111
    End If
Next c
    Set c = Nothing

Wonder if something like an active cell with an offset might work?:
With ActiveCell.Offset(, 8)
.Interior.ThemeColor = objLst.Interior.ThemeColor
.Interior.TintAndShade = objLst.Interior.TintAndShade
End With

 

[Brianwarnock]

I see you ignored this

I don't think the following line is required when bolding the entire row
c.Font.FontStyle = "Bold"

Unless the release you are on is different i cannot fathom how anything after the ELSE works
ThemeColor and TintAndShade would appear to deal with colour, not that they are keywords that I know, and therefore you would need to use Colorindex or Color keywords and if applying -0.249977111 then that is black so if applying it to the font how will you differentiate from BOLD however intandShade suggest that it is an Interior Colour thus blocking out all text, as it did when I tried it.



Brian

 

[Rx_]

Back again:
Forgot about this for a while, now back
The IF statement works OK
The Else statement only works for the entier row
otherwise, (see commented out lines) it turns the fonts clear.

4010      objXL.Range("B" & intRowPos & ":B" & intMaxRecordCount + (intRowPos)).Select
         'Debug.Print intRowPos & " start to end " & intMaxRecordCount
          ' Row B has well names in order with dates in order
          ' If Row B - bold the first 14 columns for a new name, lighten up the repeats (25 columns, only affect first 14)
4020      For Each c In objXL.ActiveWindow.Selection
4030      If c.Value <> c.Offset(-1, 0).Value Then
4040          c.Font.FontStyle = "Bold"          ' change current cell if same as cell above
4050          'c.EntireRow.Font.Bold = True  ' did not want entire row
              c.Resize(, 15).Font.Bold = True ' bolds 15 columns out  - just like customer wanted
4060      Else
4070          c.Font.ThemeColor = xlThemeColorDark1          ' works on cell if above is the same gray color
4080          c.Font.TintAndShade = -0.249977111                 ' works too
4090          c.EntireRow.Font.ThemeColor = xlThemeColorDark1 ' works on entire row
4100          c.EntireRow.Font.TintAndShade = -0.249977111        ' works on entire row
              'c.Resize(, 15).Font.TintAndShade = -0.249977111   ' nope!! cells are same color as backgound
              'c.Resize(, 15).Font.ThemeColor = xlThemeColorDark1  ' nope!! cells fonts are clear
4110      End If
4120  Next c
4130      Set c = Nothing

 

[Rx_]

comment out 4090 and 4100

Reverse the order - TintAndShade after Theme Color
c.Resize(, 15).Font.ThemeColor = xlThemeColorDark1
c.Resize(, 15).Font.TintAndShade = -0.249977111

Works great.

I don't have to post the answers. Just hope someone else will appreciate it.

 

[Brianwarnock]

I don't have to post the answers. .

No you don't but what's the point otherwise, and I would have appreciated a bit of feed back on my post #6

Brian

 

[Rx_]

Your so right. Everyone should be encouraged to post solutions.

Sorry about the lack of response to Post #6 Excel 2007 (latest SP)
Have been juggling many challanges for this release date.
Stayed late and got in very early to put out a major release this morning.

I was absolutely thrilled to get any solution for this.
It is not easy to search for, let alone find this level of answers.
Another Excel report generated from Access can use this feature. Let me plan to include it and see if I can figure it out too.
Thanks so much for your input.

 

[Brianwarnock]

Thanks for that I will now assume that the keywords I can't find are new to 2007 and stop worrying as I'm only on 2002.

Brian