[L. Howard]

Is there a boolean expression that will do this?

Cells yellow with an A or not.

Thanks
Howard

Sub CellColLetter()
'/ Ctrl + q
With Selection
If Selection.Interior.ColorIndex < 6 Then
Selection.Interior.ColorIndex = 6
Selection = "A"
Else
Selection.Interior.ColorIndex = xlNone
Selection.ClearContents
End If
End With
End Sub

[Claus Busch]

Hi Howard,

Am Sun, 26 Apr 2015 08:33:49 -0700 (PDT) schrieb L. Howard:

Is there a boolean expression that will do this?

Cells yellow with an A or not.

try:

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.ColorIndex = IIf(Target = "A", 6, xlNone)
End Sub

If this is not the expected soluntion please post more informations


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[L. Howard]

try:

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.ColorIndex = IIf(Target = "A", 6, xlNone)
End Sub

If this is not the expected soluntion please post more informations


The user will select the cells of choice and run a macro with a short cut key.

So target does not work nor change event'

Howard

[Claus Busch]

Hi Howard,

Am Sun, 26 Apr 2015 09:07:18 -0700 (PDT) schrieb L. Howard:

The user will select the cells of choice and run a macro with a short cut key.

what should the macro do? Write a A in a yellow cell or color cells with
A yellow?


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[L. Howard]

On Sunday, April 26, 2015 at 9:07:23 AM UTC-7, L. Howard wrote:
try:

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.ColorIndex = IIf(Target = "A", 6, xlNone)
End Sub

If this is not the expected soluntion please post more informations


The user will select the cells of choice and run a macro with a short cut key.

So target does not work nor change event'

Howard

Just to add, a user will select cells and run the macro, a color will be installed along with a letter. (probably the persons initial and the color may be a coding thing)

So if I select four cells and hit my Ctrl + q short cut, then cells are yellow with my letter in them.

If I select those same cells later and run the code then no color and no letter.

Howard

[Claus Busch]

Hi Howard,

Am Sun, 26 Apr 2015 09:14:41 -0700 (PDT) schrieb L. Howard:

So if I select four cells and hit my Ctrl + q short cut, then cells are yellow with my letter in them.

If I select those same cells later and run the code then no color and no letter.

then try:

Sub CellColLetter()
'/ Ctrl + q

Dim rngC As Range

For Each rngC In Selection
rngC.Interior.ColorIndex = IIf(rngC.Interior.ColorIndex = xlNone, _
6, xlNone)
rngC = IIf(rngC = "", "A", "")
Next
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[L. Howard]

On Sunday, April 26, 2015 at 9:20:40 AM UTC-7, Claus Busch wrote:
Hi Howard,

Am Sun, 26 Apr 2015 09:14:41 -0700 (PDT) schrieb L. Howard:

So if I select four cells and hit my Ctrl + q short cut, then cells are yellow with my letter in them.

If I select those same cells later and run the code then no color and no letter.

then try:

Sub CellColLetter()
'/ Ctrl + q

Dim rngC As Range

For Each rngC In Selection
rngC.Interior.ColorIndex = IIf(rngC.Interior.ColorIndex = xlNone, _
6, xlNone)
rngC = IIf(rngC = "", "A", "")
Next
End Sub


Regards
Claus B.

That does it, much cleaner than the With Selection.

Thanks.
Howard

[GS[_6_]]

That does it, much cleaner than the With Selection.

Well...

Sub CellColLetter() '/ Ctrl + q
Dim n&
With Selection
For n = 1 To Selection.Cells.Count
Cells(n).Interior.ColorIndex = _
IIf(Cells(n).Interior.ColorIndex < 6, 6, xlNone)
Cells(n) = IIf(Cells(n).Interior.ColorIndex = 6, "A", "")
Next 'n
End With
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

[GS[_6_]]

Oops.., forgot some dots...


Sub CellColLetter() '//Ctrl+q
Dim n&
With Selection
For n = 1 To Selection.Cells.Count
.Cells(n).Interior.ColorIndex = IIf(.Cells(n).Interior.ColorIndex
< 6, 6, xlNone)
.Cells(n) = IIf(.Cells(n).Interior.ColorIndex = 6, "A", "")
Next 'n
End With
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

[Claus Busch]

Hi Garry,

Am Sun, 26 Apr 2015 14:30:25 -0400 schrieb GS:

For n = 1 To Selection.Cells.Count

IMO For each ... is faster than
For n = 1 to ...
What's your opinion about loops?


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[GS[_6_]]

Hi Garry,

Am Sun, 26 Apr 2015 14:30:25 -0400 schrieb GS:

For n = 1 To Selection.Cells.Count

IMO For each ... is faster than
For n = 1 to ...
What's your opinion about loops?


Regards
Claus B.

I was only trying to demo a clean way to do 'With Selection' to Howard.
I might have gone with...


Sub CellColLetter2() '//Ctrl+Q
Dim c
For Each c In Selection
c.Interior.ColorIndex = IIf(c.Interior.ColorIndex < 6, 6, xlNone)
c = IIf(c.Interior.ColorIndex = 6, "A", Empty)
Next 'c
End Sub

...because of its brevity. Given that we don't know the size of
'Selection' in the real file it's hard to say which is fast. My opinion
is that it depends on the scenario. In my test of a 10 x 10 block of
cells the results were instantaneous!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

[L. Howard]

FYI, I offered this solution to OP and he/she was delighted.


Option Explicit

Sub Cell_Col_L()
Dim rngC As Range

For Each rngC In Selection
rngC.Interior.ColorIndex = IIf(rngC.Interior.ColorIndex = xlNone, _
3, xlNone)
rngC = IIf(rngC = "", "L", "")
Next

End Sub

Sub Cell_Col_M()
Dim rngC As Range

For Each rngC In Selection
rngC.Interior.ColorIndex = IIf(rngC.Interior.ColorIndex = xlNone, _
4, xlNone)
rngC = IIf(rngC = "", "M", "")
Next

End Sub

Sub Cell_Col_N()
Dim rngC As Range

For Each rngC In Selection
rngC.Interior.ColorIndex = IIf(rngC.Interior.ColorIndex = xlNone, _
6, xlNone)
rngC = IIf(rngC = "", "N", "")
Next

End Sub


Where apparently there are several..? users each with their own lil macro for marking and labeling or whatever. The toggle on - off was also a hit.


In the same post I was asked to look at another from OP which had no responses. I did and it is a semi-monster (to me).

I have seen these type format reconfigurations macros, and can never seem to take one and adapt it to a slightly different scenario.

If interested, the format on sheet 1 needs to be transformed to the format shown on sheet two.

Where the Q1, Q2 etc. are listed on sheet 2 only if they have an H, N or R value on sheet 1.

About 1000 rows and the Qn's extend as far as 103 per row. Rows apparently are not all the same number of columns, some 30 column, some 103 and many in between.

https://www.dropbox.com/s/9gok5nqgdz...ster.xlsm?dl=0

I would be a pure messenger on this.

Howard

[GS[_6_]]

Where apparently there are several..? users each with their own lil
macro for marking and labeling or whatever. The toggle on - off was
also a hit.

I'd go a different way where 1 macro serves all usser via the 1st
character of their username...

Sub CellCol_User() '//Ctrl+Q
Dim c
For Each c In Selection
c.Interior.ColorIndex = IIf(c.Interior.ColorIndex < 6, 6, xlNone)
c.value = IIf(c.Interior.ColorIndex = 6, Left(Environ("username"),
1), Empty)
Next 'c
End Sub

...and keep the color as criteria for the toggle because...

c.value = IIf(c = "", "A", "")

...won't remove "A". IOW, the letter persists after the color toggles.


I'll take a look at the link...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

[GS[_6_]]

If interested, the format on sheet 1 needs to be transformed to the
format shown on sheet two.

Where the Q1, Q2 etc. are listed on sheet 2 only if they have an H, N
or R value on sheet 1.

About 1000 rows and the Qn's extend as far as 103 per row. Rows
apparently are not all the same number of columns, some 30 column,
some 103 and many in between.

Well.., the logic on Sheet2 escapes me because Name4 has an "H" in Q3
but is not formatted.

The other names have the criteria in more than 1 Q, but no formatting
other than the last Q containing criteria.

In order to code this the criteria needs 'structured' logic!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

[L. Howard]

Well.., the logic on Sheet2 escapes me because Name4 has an "H" in Q3
but is not formatted.

The other names have the criteria in more than 1 Q, but no formatting
other than the last Q containing criteria.

In order to code this the criteria needs 'structured' logic!


Only the Q's are brought to sheet 2 and only if they have an Y an N or R

TYPO TYPO sorry.

Howard

[GS[_6_]]

Well.., the logic on Sheet2 escapes me because Name4 has an "H" in
Q3 but is not formatted.

The other names have the criteria in more than 1 Q, but no
formatting other than the last Q containing criteria.

In order to code this the criteria needs 'structured' logic!


Only the Q's are brought to sheet 2 and only if they have an Y an N
or R

TYPO TYPO sorry.

Howard

Okay, that helps!
Whats the logic for the formatting? (color, boldface)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

[L. Howard]

Only the Q's are brought to sheet 2 and only if they have an Y an N
or R

TYPO TYPO sorry.

Howard

Okay, that helps!
Whats the logic for the formatting? (color, boldface)


Are you asking if there is any definitive reason for some fonts in color or bold face to be applied to the reconstruction? I doubt if there is any. None were indicated.

Howard

[GS[_6_]]

Only the Q's are brought to sheet 2 and only if they have an Y an N
or R

TYPO TYPO sorry.

Howard

Okay, that helps!
Whats the logic for the formatting? (color, boldface)


Are you asking if there is any definitive reason for some fonts in
color or bold face to be applied to the reconstruction? I doubt if
there is any. None were indicated.

Howard

I'm asking why on Sheet2 are C4, C6, and C9 formatted?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

[L. Howard]

On Monday, April 27, 2015 at 4:55:43 AM UTC-7, GS wrote:

Only the Q's are brought to sheet 2 and only if they have an Y an N
or R

TYPO TYPO sorry.

Howard

Okay, that helps!
Whats the logic for the formatting? (color, boldface)


Are you asking if there is any definitive reason for some fonts in
color or bold face to be applied to the reconstruction? I doubt if
there is any. None were indicated.

Howard

I'm asking why on Sheet2 are C4, C6, and C9 formatted?


The font color is important, the bold face is preferred but not required.

Howard

[GS[_6_]]

On Monday, April 27, 2015 at 4:55:43 AM UTC-7, GS wrote:

Only the Q's are brought to sheet 2 and only if they have an Y an
N or R

TYPO TYPO sorry.

Howard

Okay, that helps!
Whats the logic for the formatting? (color, boldface)


Are you asking if there is any definitive reason for some fonts in
color or bold face to be applied to the reconstruction? I doubt if
there is any. None were indicated.

Howard

I'm asking why on Sheet2 are C4, C6, and C9 formatted?


The font color is important, the bold face is preferred but not
required.

Howard

Okay.., then what's the criteria for setting the font color (and
boldface)? It appears to be random and so can't be coded for with
structured logic!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

[L. Howard]

I'm asking why on Sheet2 are C4, C6, and C9 formatted?


The font color is important, the bold face is preferred but not
required.

Howard

Okay.., then what's the criteria for setting the font color (and
boldface)? It appears to be random and so can't be coded for with
structured logic!

You are probably correct, I have a question on such posted and awaiting an answer.

Howard

[L. Howard]

Okay.., then what's the criteria for setting the font color (and
boldface)? It appears to be random and so can't be coded for with
structured logic!

You are probably correct, I have a question on such posted and awaiting an answer.

Howard

The color formatting is done "...by a reaction" to the data, by budget people.

Therefore it has no structure for you to refer to. Done manually.

Howard

[GS[_6_]]

Okay.., then what's the criteria for setting the font color (and
boldface)? It appears to be random and so can't be coded for with
structured logic!

You are probably correct, I have a question on such posted and
awaiting an answer.

Howard

The color formatting is done "...by a reaction" to the data, by
budget people.

Therefore it has no structure for you to refer to. Done manually.

Howard

Ok, thanks...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

[L. Howard]

This just in,


The colour coding is due to the result of the qualification against the position on work sheet 1 - N = Red, R = Blue.

And Y is plain black.

Howard

[GS[_6_]]

This just in,


The colour coding is due to the result of the qualification against
the position on work sheet 1 - N = Red, R = Blue.

And Y is plain black.

Howard

Hmm...

Name1 is YYR color Blue
Name2 is YY color Red
Name3 is YNR color Blue
Name4 is YY no color

...and so does not jive with your "just in"!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

[L. Howard]

On Monday, April 27, 2015 at 4:36:48 PM UTC-7, GS wrote:
This just in,


The colour coding is due to the result of the qualification against
the position on work sheet 1 - N = Red, R = Blue.

And Y is plain black.

Howard

Hmm...

Name1 is YYR color Blue
Name2 is YY color Red
Name3 is YNR color Blue
Name4 is YY no color

..and so does not jive with your "just in"!

--
Garry

After "wringing" N = Red, R = Blue and confirming Y is no color (black of course) I asked if those were typos in the examples.

No word back yet.

It at first sounded like a committee was reviewing the data and making a decision on the color, (which has budget implications later).

I have mild confidence N = Red, R = Blue, Y = No color.

I assume if for some upshot reason R needed to be Blue, it would be a minor change in font.color = in the code.

Howard

Howard

[L. Howard]

Name1 is YYR color Blue
Name2 is YY color Red
Name3 is YNR color Blue
Name4 is YY no color

..and so does not jive with your "just in"!

--
Garry

After "wringing" N = Red, R = Blue and confirming Y is no color (black of course) I asked if those were typos in the examples.

No word back yet.

It at first sounded like a committee was reviewing the data and making a decision on the color, (which has budget implications later).

I have mild confidence N = Red, R = Blue, Y = No color.

I assume if for some upshot reason R needed to be Blue, it would be a minor change in font.color = in the code.

Howard

Howard

Reconfirmed, there were errors in the example. Colors are N = Red, R = Blue, Y = No color.

Howard

[Claus Busch]

Hi Howard,

Am Mon, 27 Apr 2015 23:19:56 -0700 (PDT) schrieb L. Howard:

Reconfirmed, there were errors in the example. Colors are N = Red, R = Blue, Y = No color.

have a look:
https://onedrive.live.com/?cid=9378A...121822A3%21326
for "Book4"

The cells are colored by CF


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[Claus Busch]

Hi Howard,

Am Tue, 28 Apr 2015 14:26:40 +0200 schrieb Claus Busch:

https://onedrive.live.com/?cid=9378A...121822A3%21326
for "Book4"

I improved Book4 with a macro to color the cells


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[L. Howard]

"

I improved Book4 with a macro to color the cells


Regards
Claus B.

Hi Claus,

Hmmm, I am thinking you may have mistaken what the code needs to do. It looks like it formats the VBA and CF sheets assuming those two sheets already are constructed as you show.

The code needs to take sheet 1 and convert it to the format of VBA and CF and then do the color coding of the Q's.

Where sheet 1 is about 1000 rows by around 100 + columns.

So, first the code has to do a massive reconstruction of sheet 1 to the new configuration and apply the color coding to the Q's.

Howard

[Claus Busch]

Hi Howard,

Am Tue, 28 Apr 2015 08:48:46 -0700 (PDT) schrieb L. Howard:

Hmmm, I am thinking you may have mistaken what the code needs to do. It looks like it formats the VBA and CF sheets assuming those two sheets already are constructed as you show.

CF and the code are looking in Sheet1. If the range in sheet1 you have
to enlarge the named ranges. The code always uses the correct ranges.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[Claus Busch]

Hi Howard,

Am Tue, 28 Apr 2015 17:53:42 +0200 schrieb Claus Busch:

CF and the code are looking in Sheet1. If the range in sheet1 you have
to enlarge the named ranges. The code always uses the correct ranges.

I changed the formulas for the named ranges. They now are adapted to the
correct range.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[L. Howard]

On Tuesday, April 28, 2015 at 8:58:19 AM UTC-7, Claus Busch wrote:
Hi Howard,

Am Tue, 28 Apr 2015 17:53:42 +0200 schrieb Claus Busch:

CF and the code are looking in Sheet1. If the range in sheet1 you have
to enlarge the named ranges. The code always uses the correct ranges.

I changed the formulas for the named ranges. They now are adapted to the
correct range.


Regards
Claus B.

The code needs to assume that CF and VBA are blank sheets to start, and must be created by the code from the huge sheet 1 layout.

Howard

[Claus Busch]

Hi Howard,

Am Tue, 28 Apr 2015 09:28:11 -0700 (PDT) schrieb L. Howard:

The code needs to assume that CF and VBA are blank sheets to start, and must be created by the code from the huge sheet 1 layout.

sorry, my bad.

To transpose the data from sheet1 to sheet4 use:

Sub Transpose()
Dim varData As Variant, varHeader As Variant
Dim varOut() As Variant
Dim i As Long, j As Long, m As Long, n As Long
Dim LRow As Long, LCol As Long, myCnt As Long

varHeader = Array("Position", "Name", "Qualification")
With Sheets("Sheet1")
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
LCol = .Cells(2, Columns.Count).End(xlToLeft).Column
For i = 4 To LCol Step 2
myCnt = myCnt + Application.CountA(.Range(.Cells(3, i),
..Cells(LRow, i)))
Next

varData = .Range(.Cells(3, 1), .Cells(LRow, LCol))
ReDim varOut(myCnt - 1, 2)
For i = LBound(varData) To UBound(varData)
m = 0
varOut(n, m) = varData(i, 1)
m = m + 1
varOut(n, m) = varData(i, 3)
m = m + 1
For j = 4 To LCol Step 2
If Len(varData(i, j)) 0 Then
varOut(n, m) = .Cells(1, j)
n = n + 1
End If
Next
Next
End With
Sheets("Sheet4").Range("A1").Resize(, 3) = varHeader
Sheets("Sheet4").Range("A2").Resize(myCnt, 3) = varOut

Sheets("Sheet4").Range("A1:C1").Font.Bold = True
Sheets("Sheet4").Range("A1:C1").HorizontalAlignmen t = xlCenter
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[L. Howard]

On Tuesday, April 28, 2015 at 9:38:33 AM UTC-7, Claus Busch wrote:
Hi Howard,

Am Tue, 28 Apr 2015 09:28:11 -0700 (PDT) schrieb L. Howard:

The code needs to assume that CF and VBA are blank sheets to start, and must be created by the code from the huge sheet 1 layout.

sorry, my bad.

To transpose the data from sheet1 to sheet4 use:

Sub Transpose()
Dim varData As Variant, varHeader As Variant
Dim varOut() As Variant
Dim i As Long, j As Long, m As Long, n As Long
Dim LRow As Long, LCol As Long, myCnt As Long

varHeader = Array("Position", "Name", "Qualification")
With Sheets("Sheet1")
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
LCol = .Cells(2, Columns.Count).End(xlToLeft).Column
For i = 4 To LCol Step 2
myCnt = myCnt + Application.CountA(.Range(.Cells(3, i),
.Cells(LRow, i)))
Next

varData = .Range(.Cells(3, 1), .Cells(LRow, LCol))
ReDim varOut(myCnt - 1, 2)
For i = LBound(varData) To UBound(varData)
m = 0
varOut(n, m) = varData(i, 1)
m = m + 1
varOut(n, m) = varData(i, 3)
m = m + 1
For j = 4 To LCol Step 2
If Len(varData(i, j)) 0 Then
varOut(n, m) = .Cells(1, j)
n = n + 1
End If
Next
Next
End With
Sheets("Sheet4").Range("A1").Resize(, 3) = varHeader
Sheets("Sheet4").Range("A2").Resize(myCnt, 3) = varOut

Sheets("Sheet4").Range("A1:C1").Font.Bold = True
Sheets("Sheet4").Range("A1:C1").HorizontalAlignmen t = xlCenter
End Sub


Regards
Claus B.
--


And if I call code Format() from Transpose code -- BINGO!

Nice job!!

Thanks Claus.

Howard