Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Fill with background color

Posted on 2015-02-22
11
Medium Priority
?
204 Views
Last Modified: 2016-02-10
Hi,
I can see the background color of the cells is like the attached. How to put the same background color to other cells, using VBA codes?
t991.png
0
Comment
Question by:HuaMinChen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 11

Author Comment

by:HuaMinChen
ID: 40625174
BTW, when running "CopyFromRecordset" within VBA codes, is there any way to get known how many records have been retrieved?
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 40625190
to set the color of a cell (range), you use:
cell.Interior.Color = vbGreen
or any other RGB color index
cell.Interior.Color = RGB(127, 187, 199)
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40625192
>when running "CopyFromRecordset" within VBA codes, is there any way to get known how many records have been retrieved?

only from checking the recordset property (rowcount) after the action;
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Author Comment

by:HuaMinChen
ID: 40625195
what should be the syntax of rowcount? Thanks
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40625201
0
 
LVL 11

Author Comment

by:HuaMinChen
ID: 40625370
Hi,
I'm retrieving records from AS 400, using these

    Dim cn As ADODB.Connection
    Dim strCNT As String, strSQL As String, strSQL2 As String, strSQL3 As String, strSQL4 As String, strSQL5 As String, strSQL6 As String
    Dim cnt As Integer, rw As Integer
    Dim rs4 As ADODB.Recordset
    Dim rs5 As ADODB.Recordset
    Dim rs6 As ADODB.Recordset
    
    strCNT = IS5ConnectionString()
    Set cn = CreateObject("ADODB.Connection")
    cn.ConnectionTimeout = SQL_TIMEOUT
    cn.Open strCNT
    
    strSQL4 = "SELECT SAAB,SAAN,SAAS,SAAPP,SABRNM,c.scact,SAPOD,SAPBR,SAPSQ,SAVFR,SADRF,SATCD,SACCY,SAAMA FROM " & HOST_LIB_EQ & "." & TBL_FM & " a," & HOST_LIB_EQ & "." & "scpf c where length(ltrim(rtrim(cast(SAPOD as char(12)))))=7 and substring(ltrim(rtrim(cast(SAPOD as char(12)))),2,6)='150130' and ltrim(rtrim(SACCY))='USD' and ltrim(rtrim(a.saan))=ltrim(rtrim(c.scan)) and ltrim(rtrim(a.saas))=ltrim(rtrim(c.scas)) and ltrim(rtrim(SAPBR))='@@AR'"
    
    Set rs4 = CreateObject("ADODB.Recordset")
    rs4.Open strSQL4, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
    
    Worksheets("EQ System Gen FX").Cells.ClearFormats
    Worksheets("EQ System Gen FX").Cells(2, 1).Value = "Overnight Position Revaluation"
    Worksheets("EQ System Gen FX").Range("A5").CopyFromRecordset rs4
    cnt = rs4.RecordCount
...

Open in new window


I don't know why cnt in above does return "-1", while I can see there are 18 records retrieved already.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40625450
I guess this is due to:
adOpenForwardOnly

which may have the effect of not filling that property
I don't see any other solution ...
0
 
LVL 11

Author Comment

by:HuaMinChen
ID: 40627331
Thanks. One last thing, what to assign to the cell if I expect to bracket the amount, when its value is negative?
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 40627501
the format needs to be like this:
#.##0;(-#.##0)

in vba, you assign that to the numberFormat property of the range:

  Dim r As Range
  Set r = Range("B:B")
  r.NumberFormat = "#.##0;(-#.##0)"

Open in new window

0
 
LVL 11

Author Comment

by:HuaMinChen
ID: 40627574
Many thanks. I put these

    Range("N" & (11 + cnt + cnt2)).NumberFormat = "#.##0;(-#.##0)"
    Range("N" & (11 + cnt + cnt2)).Formula = "=Sum(N" & (11 + cnt) & ":N" & (10 + cnt + cnt2) & ")"
...

Open in new window


but it is showing

(-9278769.0)

which is confusing, right?
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 40627603
but what do you want? without the minus sign?
in which case you just remove the " - " from the format
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
The viewer will learn how to count occurrences of each item in an array.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question