Re: Other sheets will not work in book
[ATTACH=CONFIG]60758[/ATTACH]
I am also getting an E in some of the cells when I click on the counters to go up. For example on the Numbers E-OBB row 3008 all the way across DH DN etc...
Re: Other sheets will not work in book
[ATTACH=CONFIG]60758[/ATTACH]
I am also getting an E in some of the cells when I click on the counters to go up. For example on the Numbers E-OBB row 3008 all the way across DH DN etc...
Re: Other sheets will not work in book
Very hard for me to test sheet Number E-OBB because the file you uploaded to mediafire has no data in that sheet, or indeed most of the sheets. And nothing in cell C2:F2 so I do not know what values you are using there.
If you are getting that invalid sheet message then you must be using one of the previous codes, which had that error message, not the most recent ones (post #19). Make sure that the 2 procedures in that post are the ONLY procedures in the whole VBA for the workbook (all sheet object modules must be empty and no standard modules)
This line of code:
assigns the extreme right hand letter of the target value (target is the cell that you have clicked) to a string variable (sT), then this code:
If rC.Offset(-1, 1) = "" Then
rC.Offset(-1, 1) = sT
Else: rC.Offset(-1).End(xlToRight).Offset(, 1) = sT
places that string on the sheet in the correct cell.
So, as long as the last letter of the values in C2:F2 are either E,O,U or D as required it does not matter what comes before the last letter, but each of the four cells must have a unique value. On each sheet each of the sets of 4 cells in row 1 must be identical to C2:F2 for that sheet.
As long as these conditions are met each sheet can have different values in C2:F2.
As for the extra E occurring on sheet Numbers E-OBB row 3008 all the way across, I suspect that there was already an 'E' in the row immediately above the uppermost row of data ( I noticed this being so in a couple of the sheets that did have data). Make sure that the row above the uppermost row of data (for each data set) on every sheet does not have an E,O,U or D.
Re: Other sheets will not work in book
It is working great !!! THANK YOU!!! The extra letter was coming when there was already a EO or UD in the cell next to the counter number (4digit number) Let me know if I need to a new post. I would like to incorporate when cell HIJK, OPQR .... ETC... or cells 2CDEF it will automatically place the new date on the next row up AND copy and paste a 4 blank cells where the next counter will go. Example if counter in row 3010 was next I would click on HIJ or K, it places the new counter in row 3009. In row 3008 the 4 blank formatted cells are pasted there. The blank cell is actually in place of me having to program every sheets conditional formatting to get the colors. I was thinking that may be easier to write code to copy and paste. If not , please let me know.
Re: Other sheets will not work in book
]Did you want an example of the
[TABLE="width: 128"]
[tr]
[TD="class: xl97, width: 43, bgcolor: transparent"]BB_U
[/TD]
[TD="class: xl98, width: 43, bgcolor: transparent"]BB_U
[/TD]
[TD="class: xl97, width: 43, bgcolor: transparent"]BB_D
[/TD]
[TD="class: xl98, width: 43, bgcolor: transparent"]BB_D
[/TD]
[TD="class: xl97, bgcolor: transparent"]BB_E
[/TD]
[TD="class: xl98, bgcolor: transparent"]BB_E
[/TD]
[TD="class: xl97, bgcolor: transparent"]BB_O
[/TD]
[TD="class: xl98, bgcolor: transparent"]BB_O
[/TD]
[/TABLE]
[ATTACH]60764[/ATTACH
Re: Other sheets will not work in book
You have not redone the values in cells C2:F2 as I said, BB_U, BB_U, BB_D, BB_D are NOT 4 unique values, it should be BBU, BB_U, BBD, BB_D
Also in the sample workbooks attached to posts #23 and #24 you not only do not have the 4 unique values but additionally you have not made H1:K1, O1:R1, V1:Y1 etc identical to C2:F2
Formatting can be done with the macro. Clear all conditional formatting from each sheet, then manually format the bottom row of counters for each data set and clear the contents of all other data cells. The new code which includes the formatting is below.
I have attached both books back here, 'Example of BB_U' has the corrected values, automatic date updated and no conditional formatting, and instructions re the dates and formats (READ THEM!!), 'How To Set Values' shows, yet again, how values in C2:F2 and H1:K1, O1:R1 etc. must be entered.
Option Explicit
Dim sE As String, sO As String, s1 As String, sT As String, i As Long, j As Long, rC As Range, rCC As Range
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Count <> 1 Or Target.Row > 2 Then Exit Sub
Application.ScreenUpdating = False
If Not Application.Intersect(Target, [b2]) Is Nothing Then
j = Target.End(xlDown).Row
Cells(j, 2).Copy Cells(j - 1, 2)
End If
sT = Right(Target, 1)
j = [b3].End(xlDown).Row
If Target.Row = 1 And Len(Target) < 5 And Len(Target) > 1 Then
Set rC = Target.End(xlDown): If rC.Row = Rows.Count Then Exit Sub
Call Increase
ElseIf Not Application.Intersect(Target, [c2:f2]) Is Nothing Then
With Range("g1", Cells(1, Columns.Count).End(xlToLeft))
Set rCC = .Find(Target)
If Not rCC Is Nothing Then
s1 = rCC.Address
Do
Set rC = rCC.End(xlDown): If rC.Row = Rows.Count Then Exit Sub
If Cells(j - 1, 2) = "" Then Cells(j, 2).Copy Cells(j - 1, 2)
Call Increase
Set rCC = .FindNext(rCC)
Loop While Not rCC Is Nothing And s1 <> rCC.Address
End If
End With
End If
Application.ScreenUpdating = True
End Sub
Private Sub Increase()
With rC.Offset(-1)
.Value = rC + 1
If .Value = 10 Then .Value = 1
.BorderAround (xlContinuous)
If Application.IsEven(.Value) Then
.Interior.Color = RGB(216, 216, 216)
.Font.Color = RGB(0, 156, 80)
Else
.Interior.Color = vbYellow
.Font.Color = vbRed
End If
End With
For i = 1 To 4
If Trim(rC.Offset(, -i)) <> "" Then
rC.Offset(, -i).Copy rC.Offset(-1, -i)
Else: Exit For
End If
Next
For i = 1 To 4
If Application.IsNumber(rC.Offset(, i)) Then
rC.Offset(, i).Copy rC.Offset(-1, i)
Else: Exit For
End If
Next
If rC.Offset(-1, 1) = "" Then
With rC.Offset(-1, 1)
.Value = sT
If sT = "O" Or sT = "D" Then
.Font.Color = RGB(0, 156, 80)
Else
.Font.Color = vbRed
End If
End With
Cells(j, 2).Copy rC.Offset(-1, 2)
Else
With rC.Offset(-1).End(xlToRight).Offset(, 1)
.Value = sT
If sT = "O" Or sT = "D" Then
.Font.Color = RGB(0, 156, 80)
Else
.Font.Color = vbRed
End If
End With
rC.Offset(-1).End(xlToRight).Offset(, 1) = Cells(j, 2).Text
End If
End Sub
Display More
Re: Other sheets will not work in book
Hmm, I have changed them on my MASTER sheet. I must have uploaded the wrong file. So sorry. Let me recheck and verify. Thanks KJ.
Ah!, I understand now!!! THANK YOU!!!
Re: Other sheets will not work in book
Check post #25 again, I have amended the code and changed the 'Example of BB_U' attachment, auto date updating was not quite as it should be, all OK now, sheet has instructions re the dates and formats (READ THEM!!).
Re: Other sheets will not work in book
you are by far ONE OF THE BEST programmers there are!!! THANK YOU THANK YOU THANK YOU!!!!
Re: Other sheets will not work in book
Quote from KjBox;715228Check post #25 again, I have amended the code and changed the 'Example of BB_U' attachment, auto date updating was not quite as it should be, all OK now, sheet has instructions re the dates and formats (READ THEM!!).
YES SIR!!!!:thanx:
Re: Other sheets will not work in book
I think I have this one figured out
Re: Other sheets will not work in book
Everything is working perfect except the sheet is giving me the date after the date beginning with column (AB AI AP AW BD BK BR BY CF ....MN) these columns should be empty
[ATTACH=CONFIG]60778[/ATTACH]
This is very minor and I have been messing with the Vba code but cannot seem to find the right color. The E's and U's should be bold dark green, the O's and D's should be dark bold red
Re: Other sheets will not work in book
Check that there is no date already in the row immediately above the uppermost row of data in those data sets (V1:Y1, AC1:AF1, AJ1:AM1 .......), sounds like the same issue you had before with the E and O when they were already there.
For bold and darker green (the red is as red as you can get) replace the Sub Increase() code with this:
Private Sub Increase()
With rC.Offset(-1)
.Value = rC + 1
If .Value = 10 Then .Value = 1
.BorderAround (xlContinuous)
If Application.IsEven(.Value) Then
.Interior.Color = RGB(216, 216, 216)
.Font.Color = RGB(0, 156, 80)
.HorizontalAlignment = xlCenter
Else
.Interior.Color = vbYellow
.Font.Color = vbRed
.HorizontalAlignment = xlCenter
End If
End With
For i = 1 To 4
If Trim(rC.Offset(, -i)) <> "" Then
rC.Offset(, -i).Copy rC.Offset(-1, -i)
Else: Exit For
End If
Next
For i = 1 To 4
If Application.IsNumber(rC.Offset(, i)) Then
rC.Offset(, i).Copy rC.Offset(-1, i)
Else: Exit For
End If
Next
If rC.Offset(-1, 1) = "" Then
With rC.Offset(-1, 1)
.Value = sT
If sT = "O" Or sT = "D" Then
.Font.Color = RGB(0, 150, 75)
.Font.Bold = True
Else
.Font.Color = vbRed
.Font.Bold = True
End If
End With
Cells(j, 2).Copy rC.Offset(-1, 2)
Else
With rC.Offset(-1).End(xlToRight).Offset(, 1)
.Value = sT
If sT = "O" Or sT = "D" Then
.Font.Color = RGB(0, 150, 75)
.Font.Bold = True
Else
.Font.Color = vbRed
.Font.Bold = True
End If
End With
rC.Offset(-1).End(xlToRight).Offset(, 1) = Cells(j, 2).Text
End If
End Sub
Display More
Re: Other sheets will not work in book
I understand the red is as red as I will get lol, the O and D need to be "red" right now they are green. The E's and Us need to be "green".
Also, how do I change the size of the font and style. Presently its larger than the one I have and I have to reformat every time I click on a cell. I am using 11 Calibri for my numbers (hate to be a PITA) and I do not mind changing it
Thanks Kj
Re: Other sheets will not work in book
Near the end or Sub Increase () change:
To:
Highlight cells B3 to NA3100 and set the font size and style to what you want. If you need the E, O, U or D to be a different size and/or style then highlight the relevant columns and format to required size and style.
Re: Other sheets will not work in book
Thanks Kj, duh on the B3 through NA3100, I knew better LOL
Re: Other sheets will not work in book
[ATTACH=CONFIG]60820[/ATTACH]
How can I get the digits to go to the far right when I press HIJK? I tried formatting the sheet and they still print out in the center and sometimes to the left. I have to fix every cell. Thanks!!
Re: Other sheets will not work in book
THANK YOU THANK YOU THANK YOU!!!!!!!!!!!!!!!
:dance:
Don’t have an account yet? Register yourself now and be a part of our community!