Solved: Conditional Format 2 arguments; one of them is only a part of a cell contents

[Eville]

Hello people ;)

Usually the search function works great and after some time it gets me what im looking for but not this time im afraid, altho it seems simple (i guess :D )
I have a worksheet that has product names in columns and lot numbers in rows. Now i am looking for a vba code piece that simply looks at the complete product name (eg the name "LP-NV" in D4) and if the cell also meets the condition that a lot number starts with the letter "D" in column B the cell should color gray or yellow, color isnt that important as long as it shows up.

FK1 untill FL26 shows the relation between lotnr and product names (LP-NV lot starts with D, Pro-V lot starts with P etc)
So if lot starts in a row at column B with D*** the cells D6 E6 F6 should colour, else stay white. Same goes for lotnumber starting with J*** matches the LP-NNW column etc

Once a colored cell gains data (numbers) the conditional formatted color should remain there

I marked the column matches myself with the text 'Color' to give an idea what im trying to do. I dont know if every product columns need a new piece of code? But if i have a start i can try to adjust the rest myself, altho i consider my vba knowledge as 'starter' :rofl:
There are probably a dozen ways to do this but please try to keep the code erghm simple :whistle:

The reason im looking for VBA is because it is flexible and i can use it for different sheets because i have 8 other sheets to add these CF's to.

Ohhh before i forget: i already have some vba piece in the sheet so im not sure if there can be more added to the part that starts with "Private Sub Worksheet_Change(ByVal Target As Excel.Range)" ?

Thanks for the help in advance people!! Here is the example file added.

************* not sure if i need to make a new post for this? ******************

EDIT: while im posting and searching for another issue: is it possible to round off numbers when importing them from another sheet?
Code below imports from a preselected workbook but it imports the complete number (for example 0.351256585 while i only need 3 decimals: 0.351). I think something needs to be added after the line " .Range("D10").Formula = wb.Worksheets("Whiteness").Range("$E$5").Value" but im not sure what ><
Im using this code to make the importing work (yes the code gives the error "do you want to save changes to ...." but im still working on that lol):
Sub GetDataFromClosedWorkbook()
Dim wb As Workbook
Application.ScreenUpdating = False ' turn off the screen updating
Set wb = Workbooks.Open(Filename:=Range("BF2").Value) ' Set wb = Workbooks
' open the source workbook, read only
ActiveWorkbook.Saved = True
Cancel = True
SaveAsUI = False
With ThisWorkbook.Worksheets("Dump")
On Error Resume Next
Sheets("Dump").Range("D10:D21").ClearContents
' read data from the source workbook
.Range("D10").Formula = wb.Worksheets("Whiteness").Range("$E$5").Value 'WHT Ref
.Range("D11").Formula = wb.Worksheets("Report data").Range("$P$5").Value 'WHT
.Range("D12").Formula = wb.Worksheets("Report data").Range("$U$5").Value 'SR
.Range("D13").Formula = wb.Worksheets("Sensitivity").Range("$T$5").Value 'SS
.Range("D14").Formula = wb.Worksheets("XRF MC+OC amount").Range("$E$5").Value 'MC (XRF ref)
.Range("D15").Formula = wb.Worksheets("XRF MC+OC amount").Range("$R$5").Value 'OC (XRF ref)
.Range("D16").Formula = wb.Worksheets("Elements").Range("$T$5").Value 'Elementen P
.Range("D17").Formula = wb.Worksheets("Elements").Range("$P$5").Value 'Elementen Ca
.Range("D18").Formula = wb.Worksheets("Elements").Range("$E$5").Value 'Elementen Si
.Range("D19").Formula = wb.Worksheets("Elements").Range("$I$5").Value 'Elementen AD F
.Range("D20").Formula = wb.Worksheets("Elements").Range("$I$5").Value 'Elementen AD B
.Range("D21").Formula = wb.Worksheets("Blue Residu").Range("$E$5").Value 'Elementen BR
End With
wb.Close False ' close the source workbook without saving any changes
Set wb = Nothing ' free memory
Application.ScreenUpdating = True ' turn on the screen updating
End Sub

[CatDaddy]

Sub ColoringTime()
Dim cell As Range
Dim cRow As Long
Dim sC As String
ActiveWorkbook.Sheets(1).Activate
Range("A1").Activate
For Each cell In Range("B6:B26")
cRow = cell.Row

'Left(String, #) grabs first # characters of String from left
Select Case Left(cell.Text, 1)
Case "D":
cell.Offset(0, 2).Resize(1, 3).Font.Color = RGB(255, 0, 0)

Case "J":
cell.Offset(0, 5).Resize(1, 3).Font.Color = RGB(255, 0, 0)

Case "B":
cell.Offset(0, 14).Resize(1, 3).Font.Color = RGB(255, 0, 0)

Case "S":
cell.Offset(0, 11).Resize(1, 3).Font.Color = RGB(255, 0, 0)

Case "P":
cell.Offset(0, 17).Resize(1, 3).Font.Color = RGB(255, 0, 0)

Case "L":
cell.Offset(0, 20).Resize(1, 3).Font.Color = RGB(255, 0, 0)

End Select
Next cell

End Sub


I think this aught to do it for you :)

[Eville]

Almost haha ;)


The 'color' actually is ment as a cell color (eg a light grey filled cell) ^^
How can i change the font.color to a cell.color?
Also what does the part "cell.Offset(0, 2).Resize(1, 3)" do please?
I assume the 0,2 offset is for columns D E F and the the 0,5 is for columns G H and I because the lot starts with J and J at his turn corresponds with LP-NNW ... is it possible to replace the offset by ranges, cells or directly linked to the product names?
And the range is B6:end of the B-column as i dont know yet how many lots will be there ><

At least it kinda is a start :D thx!!

[CatDaddy]

cell offset makes it so the cells effected are (x rows up or down, x columns left or right) and the resize makes makes the affected area (x rows tall, x rows wide)

it is possible to use the names as references (i dont have time to fix today, but i can help you tomorrow)!

and instead of :
cell.Offset(0, 14).Resize(1, 3).Font.Color = RGB(255, 0, 0)
you want:
cell.Offset(0, 14).Resize(1, 3).Interior.Color = RGB(255, 0, 0)

Range("B6:B26") can easily be changed to Range("B6:B" & activeLastRow) or something to that effect as well if you need it

[Eville]

cell offset makes it so the cells effected are (x rows up or down, x columns left or right) and the resize makes makes the affected area (x rows tall, x rows wide)

it is possible to use the names as references (i dont have time to fix today, but i can help you tomorrow)!

and instead of :
cell.Offset(0, 14).Resize(1, 3).Font.Color = RGB(255, 0, 0)
you want:
cell.Offset(0, 14).Resize(1, 3).Interior.Color = RGB(255, 0, 0)

Range("B6:B26") can easily be changed to Range("B6:B" & activeLastRow) or something to that effect as well if you need it
Thx for the help so far, ive added the suggested changes but no colors joy for me >< Also your attached file doesnt show any colors after adjusting the vba code there.
Im using excel 2010, does that make any difference for the codes? :dunno

Im glad your busy :P time for me to get some sleep lol ^^ thx again for the help!

[Bob Phillips]

You weren't seeting the last row



Sub ColoringTime()
Dim cell As Range
Dim cRow As Long
Dim sC As String
Dim activeLastRow As Long

ActiveWorkbook.Sheets(1).Activate
Range("A1").Activate
activeLastRow = Cells(Rows.Count, "B").End(xlUp).Row

For Each cell In Range("B6:B" & activeLastRow)

cRow = cell.Row

'Left(String, #) grabs first # characters of String from left
Select Case Left(cell.Text, 1)
Case "D":
cell.Offset(0, 2).Resize(1, 3).Interior.Color = RGB(255, 0, 0)

Case "J":
cell.Offset(0, 5).Resize(1, 3).Interior.Color = RGB(255, 0, 0)

Case "B":
cell.Offset(0, 14).Resize(1, 3).Interior.Color = RGB(255, 0, 0)

Case "S":
cell.Offset(0, 11).Resize(1, 3).Interior.Color = RGB(255, 0, 0)

Case "P":
cell.Offset(0, 17).Resize(1, 3).Interior.Color = RGB(255, 0, 0)

Case "L":
cell.Offset(0, 20).Resize(1, 3).Interior.Color = RGB(255, 0, 0)
End Select
Next cell
End Sub

[Eville]

You weren't seeting the last row
Hi xld,

Despite the slight change in the code, the cells still refuse to get a color ><
It wouldnt make any difference if im using the 2010 version or an earlier version would it?
In the meantime i keep searching and trying ^^

Ev

[Bob Phillips]

They all went red for me. 2010 shouldn't make any difference. Are you running the macro, or expecting it to be triggered by a change?

[CatDaddy]

Sub ColoringTime()
Dim productStr(1 To 7) As String
Dim i As Long, addressArr(1 To 7) As Long
Dim cRow As Long
Dim sC As String
productStr(1) = "LP-NV"
productStr(2) = "LP-NNW"
productStr(3) = "LP-NVE"
productStr(4) = "LP-NN2"
productStr(5) = "LP-NNV"
productStr(6) = "Pro-V"
productStr(7) = "Pro-VN"
For i = 1 To 7
For Each cell In Range("D4:X4")
If cell.Text = productStr(i) Then
addressArr(i) = cell.Column
End If
Next cell
Next i

ActiveWorkbook.Sheets(1).Activate
Range("A1").Activate
For Each cell In Range("B6:B26")
cRow = cell.Row

'Left(String, #) grabs first # characters of String from left
Select Case Left(cell.Text, 1)
Case "D":
Cells(cRow, addressArr(1)).Resize(1, 3).Interior.Color = RGB(205, 201, 201)

Case "J":
Cells(cRow, addressArr(2)).Resize(1, 3).Interior.Color = RGB(205, 201, 201)

Case "B":
Cells(cRow, addressArr(5)).Resize(1, 3).Interior.Color = RGB(205, 201, 201)

Case "S":
Cells(cRow, addressArr(4)).Resize(1, 3).Interior.Color = RGB(205, 201, 201)

Case "P":
Cells(cRow, addressArr(6)).Resize(1, 3).Interior.Color = RGB(205, 201, 201)

Case "L":
Cells(cRow, addressArr(7)).Resize(1, 3).Interior.Color = RGB(205, 201, 201)

End Select
Next cell

End Sub

this uses the product names in row four as the respective ranges to be altered by the select case, should turn them all grey as well

[Eville]

They all went red for me. 2010 shouldn't make any difference. Are you running the macro, or expecting it to be triggered by a change?
My humble appologies >< i placed it into the "ThisWorkbook" section while it should go into the sheet section.


this uses the product names in row four as the respective ranges to be altered by the select case, should turn them all grey as well
CatDaddy thx loads for the help, it works exactly as it should do :D
Im still working on the sheet so i might have another question or two in a few days ... is it alright if i leave this post open for abit or do i have to mark it as solved?
Thx again for the great help on both of you (i used the best bits of both lol) :bow:

Ev

If interested i can post the final file for future reference? it has more sheets lol :P

[CatDaddy]

definitely post away, and you can mark it solved and still write on it (I am subscribed and will recieve notification even if its solved for later reference)

[Eville]

definitely post away, and you can mark it solved and still write on it (I am subscribed and will recieve notification even if its solved for later reference)
Ok post is marked as solved (i was hoping the issue with google chrome and the missing 'mark' button was solved now lol, had to use the internet explorer ewwww :bug: )
Anyway thx for the subscription, i will probably go off topic tho but every question will be related to this file im afraid as its the only project im working on atm ^^

[CatDaddy]

i would like to see the combination of code you ended up using if you wouldnt mind?

[Eville]

i would like to see the combination of code you ended up using if you wouldnt mind?
Ill post what i have so far but it still has lots of problems in the workbook. The sheet for the code is the one called 'Processor' for now >< but here it is.
I had to ZIP the file (after 10 attempts to upload it, sorry staff if im spamming your mailbox :P)

[Eville]

Hi again ^^

Ive figured out the formula to add the next lot number into a column once a lot is entered. However when i change the lotnumber in sheet "producten_specificaties" the lotnumber gets overwritten so the next step i like to try is remove the formula but keep the value in a cell once the formula is 'used'

I succeeded to do something similar in the past however copy, paste and adjust the vba code doesnt seem to work and gives an error: "Variable not defined"
i probably miss one line or Dim-command but i cant figure it out arghhh lol

In the example sheet there is a value on sheet 'Processor' in cell B27. Once you switch to another sheet is should leave the valua (M333) in the cell B27 but remove the formula ... any idea why it gives the error please and how to solve it? What did i miss? :think:

Thx again in advance!