INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

(OP)
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

___________________________________________________________________________________________________________________

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

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

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

(OP)
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.

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close