Rx_
Nothing In Moderation
- Local time
- Today, 14:31
- Joined
- Oct 22, 2009
- Messages
- 2,803
Format cell value changed AND selected columns The SEQUEL [Solved]
Everyone loves this Excel Report! Including me. It has become so popular.
This automated (VBA / SQL string) report has 30 columns created from seven queries, four of which are crosstabs all joined together. Of course, the user's don't see any of that.
What users see is the very end column (30) that sometimes has a duplicate row value. And, they don't want to see that duplicate value.
Recap: This code starts in Column B. If the next ROW is a duplicate, it bolds the Column B to Column P (15) for the first instance. And for the duplicate ROWs, it changes the fonts (same columns) to a very light metalic color.
The SEQUEL (part III):
Same exact condition - If Column 30 has a value that is repeated. It needs to have its font set to clear (It could be deleted, but set it to clear is an option too).
Realize: Columns 16 to 29 contain unique non-repeating data - that do not need to be altered. So, the solution can not be to dim the entire ROW.
See comment below - where the code needs to reach out and touch only column 30.
Everyone loves this Excel Report! Including me. It has become so popular. This automated (VBA / SQL string) report has 30 columns created from seven queries, four of which are crosstabs all joined together. Of course, the user's don't see any of that.
What users see is the very end column (30) that sometimes has a duplicate row value. And, they don't want to see that duplicate value.
Recap: This code starts in Column B. If the next ROW is a duplicate, it bolds the Column B to Column P (15) for the first instance. And for the duplicate ROWs, it changes the fonts (same columns) to a very light metalic color.
The SEQUEL (part III):
Same exact condition - If Column 30 has a value that is repeated. It needs to have its font set to clear (It could be deleted, but set it to clear is an option too).
Realize: Columns 16 to 29 contain unique non-repeating data - that do not need to be altered. So, the solution can not be to dim the entire ROW.
See comment below - where the code needs to reach out and touch only column 30.
Code:
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 (30 columns, only Bold (or lighten) second through 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"
4050 'c.EntireRow.Font.Bold = True ' [B]not[/B] entire row - just selected columns
c.Resize(, 15).Font.Bold = True ' ([B]bolds[/B] test value (column B) throught 15 columns to right (column P))
4060 Else
4070 c.Font.ThemeColor = xlThemeColorDark1
4080 c.Font.TintAndShade = -0.249977111
c.Resize(, 15).Font.ThemeColor = xlThemeColorDark1
c.Resize(, 15).Font.TintAndShade = -0.249977111 ' lighten up to metalic color repeat rows 14 columns out (i.e. column B throuth P)
' [B]GOAL[/B] - sometimes have a pesky duplicate ROW value in column 30 (AE) that needs to be "clear" if it is part of the same duplicate Row
' Do Not want to affect columns in-between, just also reach-out and touch the one cell in Column AE (column 30)
4090 ' --- CODE solution HERE TO FORMAT COLUMN 30 ---
4110 End If
4120 Next c
4130 Set c = Nothing
Last edited:
