Changing the colors on excel 1

[QUILO]

Hello everybody I am new on this and I need your help


I have an inventory list on excel

The list is set as follows:
Item description price
1234 pencil 0.50
2345 pen 1.25
3456 note book 2.00

this list contains 3,000 different items
if I double click on the item number a popup window with the picture of the item will pop
if the picture do not exist a message shows saying that the picture does not exist and the item number
will change colour to red.
If I want to check witch item does not have a picture I have to double click one by one and there are a lot of numbers and work.
I would like to run a macro and check on the item column if each item has a picture or not
If the item has a picture live it on black but if don’t change colour to red
The item number is the same number as the file name but without the extension (1234.jpg) and the picture folder is on C:\Pictures2

Would some one help me on this?

THANKS
QUILO

 

[SBendBuckeye]

Hello Quillo,

Try something like the following. If you need help with setting up the variables also, just repost and I'll try to give you more detail.

For lngRow = lngRowStart To lngRowEnd
strName = Cells(lngRow, SomeColumnNumber).Value
If Len(Dir("c:\Pictures2\" & strName & ".jpg&quot) = 0 Then
Cells(lngRow, SomeColumnNumber).Font.Color = vbRed
End If
Next lngRow

Sorry, I can't test the above so the syntax may need to be tweaked a little bit, but it should be close. Let me know how it works for you.

Good Luck!

 

[QUILO]

DEAR SBendBuckeye
PLEASE HELP ME WITH THE VARIABLES OR SEND MORE DETAIL
I AM NEW ON THIS
THANKS
QUILO

 

[SBendBuckeye]

Hello Quilo,

Try this code:

Public Sub CheckPictFile(plngRowEnd As Long)
Dim lngRow As Long
Dim lngRowStart As Long
Dim lngCol As Long
Dim strName As String

'ColA = 1, ColB = 2, etc - I'll assume ColB
lngCol = 2 'ColB

'If your column headers are in row1 then start in row2
lngRowStart = 2

'We'll use a parm for the last row

'Also the Dir command returns a file if one exists or an
'empty string if no file matches the path and name

For lngRow = lngRowStart To plngRowEnd
strName = Cells(lngRow, lngCol).Value
If Len(Dir("c:\Pictures2\" & strName & ".jpg&quot) = 0 Then
'one of these three should work
Cells(lngRow, lngCol).Font.Color = vbRed
Cells(lngRow, lngCol).Font.Color = RGB(0, 255, 0)
Cells(lngRow, lngCol).Font.ColorIndex = 3
End If
Next lngRow

What this code is doing is walking down the specified column 1 row at a time from RowStart to RowEnd. For each row, it checks to see if there is a corresponding jpeg file on drive c (eg if B2 contains RedStar then it is checking to see if c:\Pictures2\RedStar.jpg exists). If it does not then it returns an empty string which has a length of zero. If the length is zero then change the color using one of the options inside the If statement.

One way to begin to learn about this stuff is to copy it into your vba code. The place your cursor over one of the words, cells or font for example and press F1. It should pop you into Help for the specified topic.

Hope this helps and good luck!

 

[QUILO]

Dear SBendBuckeye
Every thing work perfect thanks a lot for helping me
QUILO

 

[VBAjedi]

Quilo,

Don't forget to give SBendBuckeye a Star! It's the way we recognize those who help us here at Tek-Tips.

Just Click the "Mark this post as a helpful/expert post" link, then click "Ok".
VBAjedi