How to return a row color based on the value of a cell?

[codicecrm]

Hello all! I've just recently started VBA programming and am having trouble with a particular spreadsheet. Any help or insight is greatly appreciated!

I've attached color.txt. The code (Module 1) ought to color a corresponding row based on the value of a cell in a particular column of the spreadsheet. However, it will not execute when I select the values in the spreadsheet - and I'm clueless as to what I'm doing wrong.

Many thanks in advance! :)

[Guido Geurs]

dear,

First: the sub header is wrong, it must be with an underscore:

Private Sub Worksheet_Change(ByVal Target As Range)

Please explain me what you just want to do (maybe by attaching a demo sheet with data) because I don't understand the If-Then line =>

first you set=> Set myRange = Range("F2:F21") .
Then=> For Each cell In myRange
And you set in the If=> If cell.Column = 6

You loops throu "Each cell In myRange" (column 6) and you ask if it is in "Column = 6" ???

AND: myRange.Rows(ce ll.Row).Interio r.ColorIndex = iColor

But iColer is always = O because it's never set.

Please help me to understand you problem.

br,

[codicecrm]

Thanks!

Thank you so much for your response, I did figure it out and have attached a txt file with the right code. Now, I just need to figure out how to make it run faster! LOL But, at least it works! :-)

[Guido Geurs]

Dear,

Some questions on your code=

Why looping through the whole column 6 (row 1 to 65.536) when just any cell changes in the sheet?
Why looping twice for the same conditions?

I hope I understand your problem and I have attached a new code.
Hopefully is this a help for your problem.

br,

[codicecrm]

Solved

Sorry, I should have attached the original spreadsheet in the first place. I'll also provide links to other posts in the future! I apologize, I'm very new to the forums, but I'll try my best to follow protocol and be considerate!

I've attached the solution I found for the speed problem. It's way out of my league, but hopefully I'll learn something from all of you experts! ;)

Many thanks again for your help! :)

[Guido Geurs]

Dear,

No problem.
When You add a call, please give always a clear description of your problem and add as much as possible documentation to your call.
Best for Excel is the workbook (or if to complex, just the sheet ) with the problem.
Also add a screen capture of what you exactly want because it's not easy for us to understand you problem from sometimes a half code with errors and no comment lines !
Please, add also as much as possible, comment lines.
This is a great help for us and also for you (much later) when you have to modify the code.
Also : un-hide the hidden rows or columns and erase all passwords so we have a clear sight on the sheets.

Anyway, I was able to download your workbook and understand your problem.
I have attached a new workbook with (I think) the solution for your problem.

I have also some questions:
Q1= the hidden column "F" is this for the "Select case" in VBA?
If so, no need to do this because it's possible to calculate the condition with:

         Select Case .Value & .Offset(0, 1).Value

Q2= (maybe a stupid question !) is it necessary to have 2 columns for "Probabilit y of Occurrence" and "Severity Classification" ,
Why not in 1 column with "Probabilit y of Occurrence and Severity Classification" and the values "Frequent I", "Frequent II", ...

I hope this will help You with your problem.

PS:
By the way, thanks for sharing your problem with me, because I 'm learning more by solving these problems than reading a book about programming !


br,