Sleeper: Change color of variable

[russkie]

Wassup...

hey, anyone know how i can change the color of "scanned" and "thisbook" in this code to eh... lets say blue?

ActiveCell = Date & ", " & Time & ", " & "One- of BC:[ " & scanned & " ] " & "- Book ID: " & thisbook & ", Deducted from Inventory"

[Zack Barresse]

Hi russkie, what do you mean exactly? In the cell you want to change the color of it? Can you post what your variables are? What should the data in the cell look like?

[Norie]

The best way to find out how to do this would be to record a macro when you do it manually.

	ActiveCell.FormulaR1C1 = _
		"01/09/05, 17:31:48, One- of BC:[scanned ] - Book ID: thisbook , Deducted from Inventory"
	With ActiveCell.Characters(Start:=1, Length:=32).Font
		.Name = "Arial"
		.FontStyle = "Regular"
		.Size = 8
		.Strikethrough = False
		.Superscript = False
		.Subscript = False
		.OutlineFont = False
		.Shadow = False
		.Underline = xlUnderlineStyleNone
		.ColorIndex = xlAutomatic
	End With
	With ActiveCell.Characters(Start:=33, Length:=7).Font
		.Name = "Arial"
		.FontStyle = "Regular"
		.Size = 8
		.Strikethrough = False
		.Superscript = False
		.Subscript = False
		.OutlineFont = False
		.Shadow = False
		.Underline = xlUnderlineStyleNone
		.ColorIndex = 5
	End With
	With ActiveCell.Characters(Start:=40, Length:=14).Font
		.Name = "Arial"
		.FontStyle = "Regular"
		.Size = 8
		.Strikethrough = False
		.Superscript = False
		.Subscript = False
		.OutlineFont = False
		.Shadow = False
		.Underline = xlUnderlineStyleNone
		.ColorIndex = xlAutomatic
	End With
	With ActiveCell.Characters(Start:=54, Length:=8).Font
		.Name = "Arial"
		.FontStyle = "Regular"
		.Size = 8
		.Strikethrough = False
		.Superscript = False
		.Subscript = False
		.OutlineFont = False
		.Shadow = False
		.Underline = xlUnderlineStyleNone
		.ColorIndex = 5
	End With
	With ActiveCell.Characters(Start:=62, Length:=26).Font
		.Name = "Arial"
		.FontStyle = "Regular"
		.Size = 8
		.Strikethrough = False
		.Superscript = False
		.Subscript = False
		.OutlineFont = False
		.Shadow = False
		.Underline = xlUnderlineStyleNone
		.ColorIndex = xlAutomatic
	End With

[Zack Barresse]

You need to take into account the length of the varibles Norie. Plus the length of the date/time.

[MWE]

Wassup...

hey, anyone know how i can change the color of "scanned" and "thisbook" in this code to eh... lets say blue?

ActiveCell = Date & ", " & Time & ", " & "One- of BC:[ " & scanned & " ] " & "- Book ID: " & thisbook & ", Deducted from Inventory"

there may be more streamlined ways to do this, but I have found that managing the cell's content as characters works quite well. If you turn on the macro recorder and manually edit (font, color, etc) a few words in the cell, you will get something like this:

Range("E25").Select
    ActiveCell.FormulaR1C1 = "now is the time"
    With ActiveCell.Characters(Start:=1, Length:=3).Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 3
    End With
    With ActiveCell.Characters(Start:=4, Length:=8).Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    With ActiveCell.Characters(Start:=12, Length:=4).Font
        .Name = "Arial"
        .FontStyle = "Italic"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 7
    End With

As with most macro recorder generated code, you can strip out 80% of the code

[Zack Barresse]

I suggest you put Date & Time into a seperate variable, then your other text into their own variables. Makes it easier to determine lengths, which is what you'll need here.

[russkie]

nah, it cant work recording a macro where you change it manually. see the "scanned" and "thisbook" variables change size, so selecting a "position" in the cell constantly varies, the length will change with each book. Those are the barcode and the name of the books im recording into a logbook everytime they are added or subtracted from the inventory. I just want the barcode and the name of the book to stand out so it will be easier to read the Log.

[Norie]

russkie

I know the recorded macro won't work, but what it does do is give you the basic idea of how to do what you want.

You can adapt the code generated to do exactly what you want.

You need to take into account the length of the varibles Norie. Plus the length of the date/time.

I know, and the length of the text.

I'm just trying to give the OP a nudge in the right direction.

[ALe]

why don't you get the position of "scanned" and "thisbook" with the function Instr

[russkie]

but even if i have the position of those 2 variables, i need to tell it where to stop the color change. and to tell it where to stop according to their lengths is tough becuase they change.

see, i tried to just add a "change color" code before and after the variables but that doesnt work, it wont take code in the middle of defining whats inside of the cell.

i just thought there was a simpler way to just change the color of whats in those variables BEFORE i put them into the cell...?

[Norie]

Try this.

str1 = "scanned"
str2 = "thisbook"
ActiveCell = Date & ", " & Time & ", One- of BC:[" & str1 & _
			" ] - Book ID:" & str2 & " , Deducted from Inventory"
		
str3 = ActiveCell.Text
With ActiveCell.Characters(Start:=InStr(str3, str1), Length:=Len(str1)).Font
		.Name = "Arial"
		.FontStyle = "Regular"
		.Size = 8
		.Strikethrough = False
		.Superscript = False
		.Subscript = False
		.OutlineFont = False
		.Shadow = False
		.Underline = xlUnderlineStyleNone
		.ColorIndex = 5
End With
With ActiveCell.Characters(Start:=InStr(str3, str2), Length:=Len(str2)).Font
		.Name = "Arial"
		.FontStyle = "Regular"
		.Size = 8
		.Strikethrough = False
		.Superscript = False
		.Subscript = False
		.OutlineFont = False
		.Shadow = False
		.Underline = xlUnderlineStyleNone
		.ColorIndex = 5
End With

[russkie]

poyfect!

thanks alot man.

[Zack Barresse]

A very good possibility ALe. Here is an example of what I would do ...

Sub testfoo()
Dim scanned As String, thisbook As String
    Dim dt As String
    Dim dtLen As Long
    Dim sScan As Long, eScan As Long, sThis As Long, eThis As Long
    Dim txt1 As String, txt2 As String, txt3 As String
scanned = "scanned"
    thisbook = "thisbook"
dt = Date & ", " & Time & ", " & "One- of BC:[ "
    txt1 = " ] - Book ID: "
    txt2 = ", Deducted from Inventory"
    txt3 = dt & scanned & txt1 & thisbook & txt2
sScan = Len(dt) + 1
    eScan = sScan + Len(scanned)
    sThis = Len(dt) + Len(scanned) + Len(txt1) + 1
    eThis = sThis + Len(thisbook)
ActiveCell = txt3
    ActiveCell.Characters(sScan, eScan - sScan).Font.ColorIndex = 3
    ActiveCell.Characters(sThis, eThis - sThis).Font.ColorIndex = 3
End Sub

[ALe]

Wow, well done firefytr. That is what I was thinking. The fact is that I think, you do.
Thanks for the code, maybe it'll be used.
Actually I was also thinking about other methods but I couldn't find any else.

[Norie]

Sub test()
Dim str1 As String
Dim str2 As String
Dim str3 As String
    str1 = "scanned"
    str2 = "thisbook"
    ActiveCell = Date & ", " & Time & ", One- of BC:[" & str1 & _
                " ] - Book ID:" & str2 & " , Deducted from Inventory"
str3 = ActiveCell.Text
    ActiveCell.Characters(Start:=InStr(str3, str1), Length:=Len(str1)).Font.ColorIndex = 5
ActiveCell.Characters(Start:=InStr(str3, str2), Length:=Len(str2)).Font.ColorIndex = 5
End Sub