RGB colour macro issues. Can't save my macro, or even run it.

[andy7172289]

Hi all,

I need to select a particular colour from some RGB numbers, and it must be done by VBA as a formula would be limited to only 56 colours. Can someone tell me why I cannot save the macro below to use again in the future?

___________________________________________________________________________________________________________________
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, cell As Range
Dim i As Long

Set rng = Intersect(Target, Range("A:C"))
If Not rng Is Nothing Then
On Error Resume Next
For Each cell In Target.Columns(1).Cells
If Application.CountA(Range("A" & cell.Row & ":C" & cell.Row)) < 3 Or _
Application.Count(Range("A" & cell.Row & ":C" & cell.Row)) < 3 Then GoTo next_row
Cells(cell.Row, "D").Interior.Color = _
RGB(Cells(cell.Row, "A").Value, Cells(cell.Row, "B").Value, Cells(cell.Row, "C").Value)
next_row:
Next cell
End If
End Sub

___________________________________________________________________________________________________________________

 

[combo]

The workbook should be able to store macros, so you need one of xlsm or xlsb workbook formats.

To automatically execute this macro (as it is event driven one) you need:
- open the workbook with enabled macros,
- store the macro in worksheet's module, it will interact only with this worksheet.

The macro will set colour in adjacent column if:
- cells in a row, cols A-C contain numbers,
- you change a value in one of those cells (Target range), copy/paste works too,
- the change has to be done directly, (no change event by function recalculation - formula stay the same),
- the macro will affect only changed rows.

To skip row the second condition (Application.Count(Range("A" & cell.Row & ":C" & cell.Row))) is enough, also On Error... statement can be removed from the code.



combo

 

[andy7172289]

Thanks for taking the time to look at this. The macro won't actually run to begin with. When I click the 'run macro' button, the macro box comes up blank and it wont show in the 'My Workbook' drop down, either. I've tried saving it in it's own module and the excel is saved as a macro enabled workbook, too.

 

[SkipVought]

The code that you posted does not run on a Run click! It is a Worksheet Change event.

It does run as a change event! I'm not sure what it should be doing, but it runs.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Just 1) COPY your entire procedure, 2) PASTE into the Sheet Code Module (right-click sheet TAB > SELECT View Code) and the 3) DELETE your original code.

Now go to your sheet and change a value.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

Target argument is the range you change and excel automatically returns it to the event procedure you created. To do this the procedure has special name and location.
If you would like to run similar code on demand, the procedure should be public, without argument and in standard module. Replace Target by Selection and run it. The code will process crossection of selected range and columns A:C according to rules in your code.

combo