Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old October 10th, 2017, 12:06 PM
Registered User
Points: 29, Level: 1
Points: 29, Level: 1 Points: 29, Level: 1 Points: 29, Level: 1
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Oct 2017
Posts: 8
Thanks: 1
Thanked 0 Times in 0 Posts
Default If cell is any color then...

Howdy all.
This is my first time posting, and I am very beginner level so bear with me....
I am trying to write code that says if any of the cells between B1 and D1 are filled with ANY color, then fill A1 a gray...
Any Thoughts?
Reply With Quote
  #2 (permalink)  
Old October 10th, 2017, 12:13 PM
Authorized User
Points: 165, Level: 3
Points: 165, Level: 3 Points: 165, Level: 3 Points: 165, Level: 3
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Oct 2015
Location: South Africa
Posts: 39
Thanks: 0
Thanked 4 Times in 4 Posts
Default

White/default color has a specific color code, so you want to check if the color code is different than white code then do any action.
To find out what colour is white, record a macro, change to any color then reset to non-color.

I believe you do it on:
<>cell.interiorIndex.color.<\>
__________________
Nostalgia 4 Infinity
Reply With Quote
  #3 (permalink)  
Old October 10th, 2017, 12:52 PM
Registered User
Points: 29, Level: 1
Points: 29, Level: 1 Points: 29, Level: 1 Points: 29, Level: 1
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Oct 2017
Posts: 8
Thanks: 1
Thanked 0 Times in 0 Posts
Default

I already have the code for white as RGB (255, 255, 255), and the gray color I want is RGB (166, 166, 166) but I can't figure out the code... This is all I can think of so far....
Code:
If *any cell withinin* Range(B1:D1).interior.color <> RGB (255, 255, 255) 
Then Range (A1).Interior.Color = RGB (166, 166, 166)
End If
Reply With Quote
  #4 (permalink)  
Old October 10th, 2017, 01:19 PM
Registered User
Points: 29, Level: 1
Points: 29, Level: 1 Points: 29, Level: 1 Points: 29, Level: 1
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Oct 2017
Posts: 8
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Okay, my code is a little bit better now. Here is the entire sub. Still stuck...
Code:
Sub MarkAccountNumber()
'Auto fit width on all columns

    Range("A:A, DA:DA").EntireColumn.AutoFit

' Find last row

    Dim LastRow As Long             'Variable for last data row
    LastRow = Range("B5000").End(xlUp).Row

' Find first data row of spreadsheet

    Dim FirstRow As Integer         'Variable for first data row

    Cells.Find(What:="Account Number", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    FirstRow = ActiveCell.Offset(1, 0).Row

 Cells(1, "A").Activate

 'Call out variables from Excel

        Dim i As Integer

For i = FirstRow To LastRow
   *THIS LINE IS ERRORING*  If Range("C & i : CK & i").Interior.Color <> RGB(255, 255, 255) Then
    Range("A & i").Interior.Color = RGB(166, 166, 166)
    End If
    
Next i
    MsgBox "Done"
Reply With Quote
  #5 (permalink)  
Old October 10th, 2017, 04:04 PM
Authorized User
Points: 165, Level: 3
Points: 165, Level: 3 Points: 165, Level: 3 Points: 165, Level: 3
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Oct 2015
Location: South Africa
Posts: 39
Thanks: 0
Thanked 4 Times in 4 Posts
Default

Been posting but it is somehow not updating.
Heres the code below.

Sub Macro1()
Dim WB As Workbook
Dim WS As Worksheet
Dim myCell As Integer
Dim myWhite As Integer, myGrey As Integer

Set WB = Application.ThisWorkbook
Set WS = WB.ActiveSheet
myWhite = -4142 ' None Colored
myGrey = 16 ' A shade of grey

'Debug.Print WS.Cells(9, 3).Interior.ColorIndex

For i = 2 To 12
myCell = WS.Cells(i, 3).Interior.ColorIndex
If myCell <> myWhite Then
WS.Cells(i, 1).Interior.ColorIndex = myGrey
Debug.Print WS.Cells(i, 3).Interior.ColorIndex
End If
Next

End Sub
__________________
Nostalgia 4 Infinity

Last edited by Zakalwe; October 10th, 2017 at 05:00 PM.
Reply With Quote
The Following User Says Thank You to Zakalwe For This Useful Post:
onexmadxdisaster (October 10th, 2017)
  #6 (permalink)  
Old October 10th, 2017, 04:28 PM
Registered User
Points: 29, Level: 1
Points: 29, Level: 1 Points: 29, Level: 1 Points: 29, Level: 1
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Oct 2017
Posts: 8
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Wonderful! Thank you SOOO much Zakalwe!
I am definitely trying to compile and debug as often as I can, but I keep getting personal roadblocks in determining what the specific issue is.
Do you mean the immediate window? I am still trying to learn this as well.
What is the statement Debug.Print? Not sure I am following how to use this either.
Reply With Quote
  #7 (permalink)  
Old October 10th, 2017, 04:46 PM
Authorized User
Points: 165, Level: 3
Points: 165, Level: 3 Points: 165, Level: 3 Points: 165, Level: 3
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Oct 2015
Location: South Africa
Posts: 39
Thanks: 0
Thanked 4 Times in 4 Posts
Default

Code:
Sub Macro1()
Dim WB As Workbook
Dim WS As Worksheet
Dim myCell As Integer
Dim myWhite As Integer, myGrey As Integer

Set WB = Application.ThisWorkbook
Set WS = WB.ActiveSheet
myWhite = -4142 ' None Colored
myGrey = 16     ' A shade of grey

'Debug.Print WS.Cells(9, 3).Interior.ColorIndex

For i = 2 To 12
    myCell = WS.Cells(i, 3).Interior.ColorIndex
    If myCell <> myWhite Then
        WS.Cells(i, 1).Interior.ColorIndex = myGrey
        Debug.Print WS.Cells(i, 3).Interior.ColorIndex 'This is what you see in the intermediate Windw.
    End If
Next
  
End Sub
__________________
Nostalgia 4 Infinity
Reply With Quote
  #8 (permalink)  
Old October 10th, 2017, 04:47 PM
Authorized User
Points: 165, Level: 3
Points: 165, Level: 3 Points: 165, Level: 3 Points: 165, Level: 3
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Oct 2015
Location: South Africa
Posts: 39
Thanks: 0
Thanked 4 Times in 4 Posts
Default

Code:
Sub Macro1()
Dim WB As Workbook
Dim WS As Worksheet
Dim myCell As Integer
Dim myWhite As Integer, myGrey As Integer

Set WB = Application.ThisWorkbook
Set WS = WB.ActiveSheet
myWhite = -4142 ' None Colored
myGrey = 16     ' A shade of grey

'Debug.Print WS.Cells(9, 3).Interior.ColorIndex

For i = 2 To 12
    myCell = WS.Cells(i, 3).Interior.ColorIndex
    If myCell <> myWhite Then
        WS.Cells(i, 1).Interior.ColorIndex = myGrey
        Debug.Print WS.Cells(i, 3).Interior.ColorIndex
    End If
Next
  
End Sub
__________________
Nostalgia 4 Infinity
Reply With Quote
  #9 (permalink)  
Old October 10th, 2017, 04:49 PM
Authorized User
Points: 165, Level: 3
Points: 165, Level: 3 Points: 165, Level: 3 Points: 165, Level: 3
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Oct 2015
Location: South Africa
Posts: 39
Thanks: 0
Thanked 4 Times in 4 Posts
Default

Sub Macro1()
Dim WB As Workbook
Dim WS As Worksheet
Dim myCell As Integer
Dim myWhite As Integer, myGrey As Integer

Set WB = Application.ThisWorkbook
Set WS = WB.ActiveSheet
myWhite = -4142 ' None Colored
myGrey = 16 ' A shade of grey

'Debug.Print WS.Cells(9, 3).Interior.ColorIndex

For i = 2 To 12
myCell = WS.Cells(i, 3).Interior.ColorIndex
If myCell <> myWhite Then
WS.Cells(i, 1).Interior.ColorIndex = myGrey
Debug.Print WS.Cells(i, 3).Interior.ColorIndex
End If
Next

End Sub
__________________
Nostalgia 4 Infinity
Reply With Quote
  #10 (permalink)  
Old October 10th, 2017, 04:52 PM
Authorized User
Points: 165, Level: 3
Points: 165, Level: 3 Points: 165, Level: 3 Points: 165, Level: 3
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Oct 2015
Location: South Africa
Posts: 39
Thanks: 0
Thanked 4 Times in 4 Posts
Default

Been posting but it looks like the page is slow to update.
Don't be surprised if you see several posts of the code.
__________________
Nostalgia 4 Infinity
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to get Excel Cell color populate .NET Framework 2.0 2 March 3rd, 2014 03:25 AM
need help with fill in cell color pooky Excel VBA 1 July 29th, 2012 09:58 PM
change cell color martin1 Visual Basic 2005 Basics 2 October 4th, 2006 08:55 AM
Change Cell Color phungleon HTML Code Clinic 2 April 9th, 2006 12:36 AM
Listview Cell Color leontun Pro VB 6 2 December 15th, 2004 03:14 PM



All times are GMT -4. The time now is 09:20 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.